Options to be specified in SQL control files which call multiple scripts

SET DEFINE OFF - if there are special characters to be inserted

SET ECHO ON - to get SQL statements executed along with their execution results.

SPOOL <path\file_name> - file name for spooling output. Make sure the user which runs the script has write permission in the directory where spool file will be written, else after scripts are complete, you will find yourself scratching your head for that missing spool file : )

SET TIMING ON - to get timings of various sql statements executed.

SET TERMOUT ON

SET TIME ON – to get time on command prompt. Quite useful when master control file runs many many queries and takes hours/minutes to run.

Oracle Multi - Table Inserts

Many a times we forget the power of SQL and resort to PL/SQL for simple requirements. Conditional insert is one such example which can be handled easily by Multi-Insert SQL queries as against writing PL/SQL blocks. Sample:

INSERT

WHEN (<condition>) THEN

  INTO <table_name> (<column_list>)

  VALUES (<values_list>)

WHEN (<condition>) THEN

  INTO <table_name> (<column_list>)

  VALUES (<values_list>)

ELSE

  INTO <table_name> (<column_list>)

  VALUES (<values_list>)

SELECT <column_list> FROM <table_name>;

 

Check out below sample taken from http://www.pythian.com/news/463/oracles-little-known-multi-table-insert by Babette Turner-Underwood

 

INSERT

   when ( id is null )

       insert into dept_exception

       values ( dept_rec.dept_id, dept_rec.dept_name, 'No employees')

   when ( comm > sal )

       insert into emp_exception

       values ( rec.id, rec.name, rec.sal, rec.comm, rec.dept_id, 'comm greater than sal')

   when ( first_dept = 'Y')

       insert into new_dept

       values ( dept_id, dept_name)

   when ( nvl(sal,0) >= nvl(comm,0) )

           insert into new_emp

           values ( id, name, dept_id, sal, comm)

SELECT dept.dept_id, dept_name, emp_id id, emp_name name, sal, comm

               from dept , emp

              where dept.dept_id = emp.dept_id(+);

 

A variation of INSERT WHEN is INSERT ALL which unconditionally inserts into all tables specified.

INSERT ALL

INTO <table_name> VALUES <column_name_list>

INTO <table_name> VALUES <column_name_list>

...

SELECT Statement;

 

Oracle Foreign Keys with NULLS

Here is a fact that even some experienced oracle developers don’t know. By definition a Primary Key – Foreign Key relationship means that all the values in child table’s Foreign Key column should exist in Parent Table’s Primary Key column. However, we ASSUME that since parent table can not have NULLs in Primary Key, child table also can not have NULLs in foreign key since. However this is not true. We can indeed have NULLs in foreign key on a table referring to a valid primary key in parent table.