Search code examples
oraclesqlplusspool

SQLPlus dynamic spool filename

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

Solution

  • 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;