SQL query inside a SQL Script

Question

I have a SQL script, which spools data to a file. Sample Existing SQL script:

whenever sqlerror exit failure rollback 
spool test.txt
set serveroutput on
select * from emp;
spool off
/

But, I would like to write a SQL query in this script before spooling data. I don't want to hardcode the name of the spooling file, so how could I get the file name from a table or lookup?

I want the code to be something like

var filename varchar2(30);
select fname into :filename from table where script = 'abcscript';
spool :filename
set serveroutput on
select * from emp;
spool off
/

Thanks.

Vikram · Accepted Answer · 2012-03-18 05:21:00Z

3

COLUMN spool_file_name NEW_VALUE.spool_file_name NOPRINT 

select fname spool_file_name 
 from table where script = 'abcscript'; 

SPOOL &spool_file_name 

SET ECHO ON 

 select * from emp ;

SPOOL OFF 
COLUMN spool_file_name CLEAR

edited Mar 18, 2012 at 5:21

answered Mar 18, 2012 at 5:14

Vikram

8,32335 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL query inside a SQL Script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related