CODEWITHSUNDEEP
oracle-databasesqlplus
Dzyann
Dzyann

Reputation: 5218

How to spool to different files from nested scripts?

Is it possible to spool to different files from nested script? I have one script that calls another script. The first script spools everything to an output. And I want to save only certain parts of the second script in a different file.

But when I stop spooling in the second script, it stops completely, and I don't know how to start it again to make it continue spooling in the first file.

For example having these 2 scripts, the second Select to MY_TABLE doesn't get spooled.

firstScript.sql

prompt --------------Start firstScript--------------
set pagesize 0
set heading on 
set feedback on 
set timing on
set time on
set echo on
set verify on

spool testFirstScript.sql

select DBMS_METADATA.GET_DDL('TABLE', 'MY_TABLE') from dual;

@secondScript.sql

select * from MY_TABLE where Id = 1; -- This doesn't get spooled.

spool off

prompt --------------End firstScript--------------

secondScript.sql

prompt --------------Start secondScript--------------

spool testSecondScript.sql

select * from MY_TABLE;

spool off
prompt --------------End secondScript--------------

The spool output:

testFirstScript.sql

11:28:23 SQL> 
11:28:23 SQL> select DBMS_METADATA.GET_DDL('TABLE', 'MY_TABLE') from dual;

  CREATE TABLE "S1"."MY_TABLE"                                             
   (    "ID" NUMBER,                                                               
    "NAME" VARCHAR2(30)                                                            



1 row selected.

Elapsed: 00:00:01.08
11:28:24 SQL> 
11:28:24 SQL> @secondScript.sql
11:28:24 SQL> prompt --------------Start secondScript--------------
11:28:24 > 
--------------Start secondScript------------- 
11:28:24 SQL> spool testSecondScript.sql

testSecondScript.sql

11:28:24 SQL> 
11:28:24 SQL> select * from MY_TABLE;
         1 Test1                                                                
         2 Test2                                                                

2 rows selected.

Elapsed: 00:00:00.36
11:28:24 SQL> 
11:28:24 SQL> spool off

Upvotes: 4

Views: 3149

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

When you run a script with @ the contents of that file are embedded in the parent, so from SQL*Plus' point of view you're just running one long script. SQL*Plus commands are not localised to the child script.

You would have to start spooling again after the embedded script, with the APPEND clause, e.g. in firstScript.sql:

...
spool testFirstScript.sql

select DBMS_METADATA.GET_DDL('TABLE', 'MY_TABLE') from dual;

@secondScript.sql

-- restart spooling
spool testFirstScript.sql append

...

With that change, testFirstScript.sql now has:

16:28:14 SQL>
16:28:14 SQL> select DBMS_METADATA.GET_DDL('TABLE', 'MY_TABLE') from dual;

DBMS_METADATA.GET_DDL('TABLE','MY_TABLE')
--------------------------------------------------------------------------------

  CREATE TABLE "STACKOVERFLOW"."MY_TABLE"
   (    "ID" NUMBER,
    "NAME" VARCHAR2(


1 row selected.

Elapsed: 00:00:00.26
16:28:14 SQL>
16:28:14 SQL> @secondScript.sql
16:28:14 SQL> prompt --------------Start secondScript--------------
16:28:14 >
--------------Start secondScript-------------
16:28:14 SQL> spool testSecondScript.sql
16:28:14 SQL>
16:28:14 SQL> select * from MY_TABLE where Id = 1; -- This doesn't get spooled.
16:28:14   2
16:28:14 SQL> spool off

And testSecondScript.sql still has:

16:28:14 SQL>
16:28:14 SQL> select * from MY_TABLE;

no rows selected

Elapsed: 00:00:00.00
16:28:14 SQL>
16:28:14 SQL> spool off

You might also want to spool off before @secondscript. And set long 32000 (or some other big number) so you see the whole dbms_metadata output, though I realise this is just an example.

Upvotes: 6

Related Questions