RSS

Category Archives: DBA

Oracle Database Administrators

SPOOL APPEND – Workaround to achieve nested spool in Oracle SQL Scripts

Last updated on November 26th, 2014 at 07:07 am

Have you ever tried to execute nested scripts with spool within each file. There may have been instances when you might have had numerous sql scripts, with each having their own spool and you might have wanted to invoke them using a main script.
Like as follows:

SPOOL main.log;
.<main.sql>
.SPOOL inner1.log;
..<inner1.sql>
.SPOOL OFF;
..SPOOL inner1_inner.log;
…<inner1_inner.sql>
..SPOOL OFF;
.<sql_statement_1>
.<sql_statement_2>
SPOOL OFF;

Problem:

In above example you might want to have separate spool file for each of the scripts viz. main.log, inner1.log and inner1_inner.log.

But you cannot achieve this using traditional spool command prior to 10g client, where nesting of spool command was not allowed and in above scenario the main.log won’t show the result of <sql_statement_1> and <sql_statement_2>.

Tip:

Starting Oracle 10g Client you can use SPOOL APPEND command which will append to the end of existing file and this can be used a workaround to achieve the nesting of spools

In the above example I can re-write the script as

SPOOL main.log;
.<main.sql>
SPOOL OFF;
.SPOOL inner1.log;
..<inner1.sql>
.SPOOL OFF;
..SPOOL inner1_inner.log;
…<inner1_inner.sql>
..SPOOL OFF;
SPOOL main.log APPEND;
.<sql_statement_1>
.<sql_statement_2>
SPOOL OFF;

By adding the text highlighted in red you can get the output of sql_statement_1 and sql_statement_2 in main.log itself, which was missed out due to traditional nesting of spool in version of oracle client prior to 10g.

For more enhancement of SPOOL command (CREATE, REPLACE) you can visit Oracle Documentation on Spool command here.

 
1 Comment

Posted by on February 18, 2013 in DBA, Oracle

 

Tags: , ,

Image

Understanding Performance Tuning in Oracle – Google Hangout Event

Last updated on November 26th, 2014 at 07:07 am

Dear Friends,
I will be presenting an online session on Oracle Performance Tuning. Its a Google Hangout Event to be conducted on 2nd Feb 2013, Saturday starting at 5:00 pm IST.

For details and registration please click register or click on below screenshot.

Understanding Performance in Oracle - Google Hangout Event

Looking forward to catch you all there.

For the events and session conducted by me in past you can visit the events archive section here.

 
1 Comment

Posted by on January 26, 2013 in DBA, Oracle, PL/SQL

 

Tags: ,

 
snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake snowflake