Generate the output in same spool in Oracle

Question

SPOOL c:\out.SPL

    SELECT * FROM a WHERE ACCOUNT_NUMBER='&CRN' ;
    SELECT * FROM b WHERE ACCOUNT_NUMBER='&CRN' ;

    Spool off
    accept op prompt 'Do u want to generate el spool,type y ' default 'n'
    SAVE c:\query2.SQL 
       select * from getm_liab   where liab_no='a' ;

    column c me new_value x
    set termout off
    select decode(lower('&op'),'y','C:\query2.SQL','n', 'null') c from dual;

    spool c:\out.SPL APPEND
    @&x

The above script is I run set of queries first and I will run remaining set of queries only when user types y. But it is not working please suggest

I am not getting any error but not getting what i wanted..The second set of queries is not running — user3737084, Jun 26 '14 at 11:11

score 0 · Accepted Answer · answered Jun 26 '14 at 13:03

content of q1.sql:

accept op prompt 'Do u want to generate el spool,type y ' default 'n'

column c new_value x
set veri off
set echo off
set head off
spool qyn.sql
select decode(lower('&op'),'y','@','n', '--')||'q2'  from dual;
spool off

@qyn '&1'

content of q2.sql:

select 'boe' from dual where dummy = '&1';
select 'oops' from dual where dummy = '&1';

sqlplus foo/far@orcl @q1 X output with Y:

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 26 15:00:13 2014

Copyright (c) 1982, 2012, Oracle.  All rights reserved.


Verbonden met:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Do u want to generate el spool,type y y

@q2


boe


oops

output with N:

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 26 15:00:54 2014

Copyright (c) 1982, 2012, Oracle.  All rights reserved.


Verbonden met:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

Do u want to generate el spool,type y n

--q2

This is one of the ways to handle this. Easiest is to use the positional variables but you can also define them as named variables.

Generate the output in same spool in Oracle

1 Answers1