SQLPlus dynamic spool filename

Question

i have an Oracle db and i want to export data to a file.However filename, extension and separator will take value from table. The problem is that i can't use the values from table. Could you suggest me a way to do it? Or if i can do this with batch?

Table(id, path, filename, extension, separator)

script.sql

conn ....
variable fullpath varchar2(20);
variable filename varchar2(10);  
variable extension varchar2(5);  
variable sep varchar2(1);

begin
  select filename, path, extension,separator
  into :filename, :fullpath, :extension, :sep
  from Table;
end;
/

set separator sep

spool fullpath||filename||'.'||extension;
... select queries...
spool off;

Regards

I googled "sqlplus dynamic spool file name" and came up with lots of hits. The very first was from Ask Tom and outlined a method that you should be able to adapt.[link]( https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:3581757800346555562) — EdStevens, Oct 05 '16 at 21:27

Alex Poole · Accepted Answer · 2016-10-05T22:40:27.197

3

You could use substitution variables and the new_value clause of the column command.

conn ....

column spool_path new_value sub_spool_path noprint
column sep new_value sub_sep noprint
set verify off
set termout off

select path || filename ||'.'|| extension as spool_path, separator as sep
from Table;

set termout on

set separator &sub_sep

spool &sub_spool_path
... select queries...
spool off;

edited Oct 05 '16 at 22:40

answered Oct 05 '16 at 22:35

Alex Poole

183,384
11
179
318

score 2 · Answer 2 · answered Oct 05 '16 at 21:28

SPOOL is a SQLPlus command, so you can not use it in a PlSQL block dynamically.

One way could be creating at runtime a second script, dynamically built based on your query, and then run it to do the job. For example:

conn ...
set serveroutput on
set feedback off
variable fullpath varchar2(20);
variable filename varchar2(10);  
variable extension varchar2(5);  
variable sep varchar2(1);
/* spool to a fixed file, that will contain your dynamic script */
spool d:\secondScript.sql
begin
  select 'filename', 'd:\', 'txt', '|'
  into :filename, :fullpath, :extension, :sep
  from dual;

  /* write the second script */

  dbms_output.put_line('set colsep ' || :sep);
  dbms_output.put_line('spool ' || :fullpath || :filename || '.' || :extension);
  dbms_output.put_line('select 1, 2, 3 from dual;');
  dbms_output.put_line('spool off');
end;
/
spool off  

/* run the second script */
@d:\secondscript.sql

This gives:

SQL> sta C:\firstScript.sql
Connected.
set colsep |
spool d:\filename.txt
select 1, 2, 3 from dual;

         1|         2|         3
----------|----------|----------
         1|         2|         3

d:\filename.txt:

         1|         2|         3                                                
----------|----------|----------                                                
         1|         2|         3

SQLPlus dynamic spool filename

2 Answers2