1.Copy the structure of an existing table with ease


Copy the structure of an existing table with ease. One can copy the structure of an existing table, not just in Oracle but in any SQL-supported database with ease. We will see how you can do the same in oracle using a single create statement, say you have a table "student_master", for which you want to create an audit table named "student_master_audit" having the same structure as "student_master" table without any data.

SQL> create table student_master_audit as select * from student_master where 1=2;

Explanation: Since 1 will never be equal to 2 so data will never get copied on the structure will be copied.


2.DML Error Logging Feature


This was introduced newly in oracle10g and is part of oracle pl sql.This feature allows us to capture exceptions i.e. duplicate or bad records that do not meet criteria defined in the table. In this feature oracle creates a error table with structure similar to original table in which the error records along with what was the error is captured.

Generally while transferring data from staging tables to main tables we get lot of errors, eg:data-migration scenarios. In such cases even if a single record fails the whole transaction fails and the complete transaction gets rolled back as per the code-logic. And it is very annoying for a developer to mine for the error record which has to be done with the help of sophisticated queries.

DML error logging comes to the rescue here wherein the error record is stored separately in the error table and the transaction succeeds, even threshold limit can be set to control limit of error records.

Further reading on this feature can be done on following link Oracle-Developer.net/DML Error Logging


3.Quoting String literals in Oracle10g


It has always been a pain for Application developers when they come across apostrophes in a given sentence, like writing a single sentence such as "John's car is at the Garage" may cause lot of hardships. With several workaround including putting multiple single quotes to solve the issue looks like a satisfactory solution to this problem. To overcome such difficulties oracle 10g has introduced Quoting string, here you write any string between q'[]' and you put anything and everything in the string and your string appears as it is.

Further reading on this can be done on the following link Oracle-Developer.net/Quoting String Literal