RSS

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

18 Feb

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 18 February, 2013 in DBA, Oracle

 

Tags: , ,

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

  1. SutoCom

    19 February, 2013 at 4:59 am

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: