oracle - SQLPlus 动态假脱机文件名

Question

我有一个 Oracle 数据库，我想将数据导出到文件。但是文件名、扩展名和分隔符将从表中获取值。问题是我无法使用表中的值。你能建议我一种方法吗？或者我是否可以通过批处理来做到这一点？

表(id、路径、文件名、扩展名、分隔符)

脚本.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;

问候

Answer 1

您可以使用替换变量和 the new_value clause的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;