Ask Anantha

I had recently attended OTN Database 12c Developer Day, at Bangalore. I must say it was very informative and gave me new insights on the new database unveiled recently by Oracle Corporation. Rather than focussing more about the event, I will focus what was being the agenda or what what I learned from this event. I must admit I am getting old because this cloud business is too hard to understand. You will agree with me if you see advertisements of cloud-ready, but are not able to understand anything beneath it. This event gave me the initial thoughts. Before 12c, Oracle databases supported multi-tenancy through VPD or FGAC (whatever you prefer to call it). To use this feature, your tables needed to have extra columns to identify a tenant when he is logging in to the database. So with a single database, you could (a big COULD) support multiple clients. But it was used not for this purpose (maybe oracle knew this before). Application developers took this feature to implement ...

Have you heard of JSP? Have you heard of ASP? Have you heard of PHP? Of course I have heard of them all. They all does one function, generate web pages dynamically to deliver a nice and rich front-end to the web. But Have you heard of PSP? What? PSP is acronym for Oracle's PL/SQL Server Pages. Oracle has this kind of capability? Well Oracle always has this sort of capability but it was called in a rather different name. It was and is called PL/SQL Web Toolkit. But rather unknown or less used fact is Oracle also has an extension to this. This is called as PSP. I have created a whitepaper which is here for you to read: For those who are unable to view the presentation in this web page or to view in Full screen, click here .

One point which is usually overseen with the usage of TO_CHAR() is the leading space that it returns when converting a number to character . Example: select length(to_char('109','000.00')) from dual; TO_CHAR('109','000.00') " 109.00" Do you notice the leading space that TO_CHAR() has returned? If you have not noticed and surprised, execute this and find it for yourself. This is not a bug. The Reason is Oracle reserves the first character for Sign. If you won't beleive me, execute the following statement: select to_char('-109','000.00') from dual; TO_CHAR('-109','000.00') "-109.00" Notice that the length of the string has not increased with the negative value being converted by TO_CHAR(). Hold on your tendency of calling TRIM() function to trim out the space. TO_CHAR() has a format model that suppresses this trailing space. To suppress this leading space simply use the FM (stands for...

Prior to Oracle 11g, we have used PLS_INTEGER data type in PL/SQL programs. In 11g, a new data type SIMPLE_INTEGER has been introduced. It is a sub-type of PLS_INTEGER data type and has the same range as PLS_INTEGER. The basic difference between the two is that SIMPLE_INTEGER is always NOT NULL . When the value of the declared variable is never going to be null then we can declare it with SIMPLE_INTEGER data type. Another major difference is that it never gives numeric overflow error like its parent data type instead it wraps around without giving any error. When we don’t have to worry about null checking and overflow errors, SIMPLE_INTEGER data type is the best to use. Posted by decipherinfosys , More information check Oracle Documentation

Virtual Columns has been introduced in Oracle Database 11g Release 1. Here is a good tutorial I could find from Oracle-Base website. The link for the tutorial is at the bottom of this article. - Anantha When queried, virtual columns appear to be normal table columns, but their values are derived rather than being stored on disc. The syntax for defining a virtual column is listed below. column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL] If the datatype is omitted, it is determined based on the result of the expression. The GENERATED ALWAYS and VIRTUAL keywords are provided for clarity only. The script below creates and populates an employees table with two levels of commission. It includes two virtual columns to display the commission-based salary. The first uses the most abbreviated syntax while the second uses the most verbose form. CREATE TABLE employees (  id          NUMBER,  first_name  VARCHAR...

This feature is part of trigger enhancements released in Oracle 11g Release 1. This particular enhancement paves way to use a single trigger to perform more tasks which could'nt have been possible before. For example, you can use a single trigger for row triggers as well as row-level triggers. The syntax for a compound trigger is as follows: CREATE OR REPLACE TRIGGER triggername   FOR triggeraction ON tablename   COMPOUND TRIGGER   -- Variable declaration.   BEFORE STATEMENT IS   BEGIN     /*Some PL/SQL Code here*/   END BEFORE STATEMENT;   BEFORE EACH ROW IS   BEGIN     /*Some PL/SQL Code here*/   END BEFORE EACH ROW;   AFTER EACH ROW IS   BEGIN     /*Some PL/SQL Code here*/   END AFTER EACH ROW;   AFTER STATEMENT IS   BEGIN     /*Some PL/SQL Code here*/ ...

Sequence number generation in a query has been almost natural in reporting queries. You normally tend to use rownum pseudo column from either your table or if it is a generic query user_objects. For example: select rownum from user_objects where rownum The output will be: ROWNUM --------------- 1 2 3 . . 100 This is based on the assumption that there are more number of objects in user_objects view so as to return a high number. The more robust way to get an output without using such a large view was not literally possible in pre-9i era. Starting 9i Oracle has come up with a beautiful solution. You can use DUAL table in conjunction with CONNECT BY clause to come up with such a result. Example: select level from dual connect by level This also will generate the same output generated in our first case.

This is a step-by-step guide on the simplest of the simplest ways by which data can be uploaded from CSV file to Oracle table. I have done uploading using this way for as many as 10,000 records without any performance issues. It went very swiftly. Login to OracleXE first and follow the instructions below: Step 1 - Click Utilities Step 2 - Click Data Load/Unload Step 3 - Click Load Step 4 Step 5 - Click Load Spreadsheet Data Step 6 - Follow the screen Step 7 - Follow the screen Step 8 - Follow the screen Step 9 - Follow the screen Step 10 - Follow the screen

To compile an entire schema in Oracle, there are two utilities provided by Oracle. DBMS_UTILITY.COMPILE_SCHEMA Two procedures in UTL_RECOMP DBMS_UTILITY.COMPILE_SCHEMA This package prior to 10g would recompile all objects (prior to Oracle 10g) and optionally recompile all Invalid Objects (starting from Oracle 10g). This procedure is available with all schemas (there is no need for giving additional privileges). I am using Oracle 10g (10.2.0.3.0) for the following examples. Syntax: DBMS_UTILITY.COMPILE_SCHEMA( schema VARCHAR2, compile_all BOOLEAN, reuse_settings BOOLEAN); Example (from SQL *Plus): EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT', compile_all => FALSE); For recompiling all objects irrespective or VALID or INVALID use the following example (simply omit the compile_all parameter): EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'SCOTT'); UTL_RECOMP Package This package has been introduced from Oracle 10g Release 2 , which is ...

Multitable inserts allow a single INSERT INTO .. SELECT statement to conditionally, or non-conditionally, insert into multiple tables. This statement reduces table scans and PL/SQL code necessary for performing multiple conditional inserts compared to previous versions. It's main use is for the ETL process in data warehouses where it can be parallelized and/or convert non-relational data into a relational format. --Unconditional Insert into all tables INSERT ALL INTO ap_cust VALUES (customer_id, program_id, delivered_date) INTO ap_orders VALUES (order_date, program_id) SELECT program_id, delivered_date, customer_id, order_dateFROM airplanes; -- Pivoting insert to split non-relational data INSERT ALL INTO Sales_info VALUES (employee_id,week_id,sales_MON) INTO Sales_info VALUES (employee_id,week_id,sales_TUE) INTO Sales_info VALUES (employee_id,week_id,sales_WED) INTO Sales_info VALUES (employee_id,week_id,sales_THUR) INTO Sales_info VALUES (employee_id,week_id, sales_FRI) SELEC...

If your DML statement encounters an exception, it immediately rolls back any changes already made by that statement, and propagates an exception out to the calling block. Sometimes that's just what you want. Sometimes, however, you'd like to continue past such errors, and apply your DML logic to as many rows as possible. DBMS_ERRLOG, a package introduced in Oracle10g Release 2 , allows you to do precisely that. Here is a quick review of the way this package works.  First you need to create an error log table where the errors will be inserted, and while issuing any DML statements use a clause newly introduced in 10g, LOG ERRORS. 1. Create an error log table for the table against which you will execute DML statements: BEGIN   DBMS_ERRLOG.create_error_log (dml_table_name => 'EMP'); END; Oracle then creates a table named ERR$_EMP that contains error-related columns as well as VARCHAR2 columns for each of your table's columns (where it...

Oracle Tips by Burleson Consulting Today, systems with thousands of individual Oracle user ID's are not very common.  Almost all enterprise applications (ERP packages like SAP, Oracle Financials) use pre-spawned Oracle connections that are created by a single Oracle user ID with names like AR or SAPR3. The application server manages the connections to Oracle for us. This is called a "proxy", the term proxy meaning an act where a 3rd party does something on our behalf.   Unfortunately, when our end-users connect anonymously through a proxy, we do not have any end-user level security through traditional security tools like granting privileges or using roles and the only Oracle security is that is granted the ERP "master" user ID. Who goes there? From a DBA perspective, all database activity is being done by this single user and a query of the v$session view for an ERP does not identify any specific user, because they are connected to Oracle via the proxy o...

There are plenty of high-level data warehousing features in Oracle for instance that you might never need. Moreover, there may be some other features such as ACID compliant transactions that you just can't do without. We'll look at the major ones, such as stored procedures, views, snapshots, table datatypes, transactions, and so on. We'll look at Postgresql, MySQL, and Oracle and discover which ones do what you need.   Database Stored Procedures. In a stored procedure the data is read, manipulated, and updated in one step. Meanwhile if you did the same in your middle tier application code, you would have to send that data set over the network, do your manipulations, and send it back. Not only would it make this one task slower, but other transactions vying for that same data could potentially have to wait while that data is in transit, and being manipulated. Also, stored code can serve to encapsulate specific requests which can be invaluable at simplifying your over...

Sreeram Surapaneni , svsreeram@yahoo.com Introduction: Oracle 10g offers several new features, one of which is Data Pump technology for fast data movement between databases. Most Oracle shops still use their traditional export and import utility scripts rather this new technology. Data Pump technology is entirely different from the export/import utility, although they have a similar look and feel. Data Pump runs inside the database as a job, which means jobs are somewhat independent of the process that started the import or export. Another advantage is that other DBAs can login to the database and check the status of the job. The advantages of Data Pump, along with Oracle's plan to deprecate the traditional import/export utilities down the road, make Data Pump a worthwhile topic for discussion. Oracle claims Data Pump offers a transfer of data and metadata at twice the speed of export and twenty to thirty times the speed of the import utility that DBAs have been using fo...