oracle SQLPlus 动态假脱机文件名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39883668/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQLPlus dynamic spool filename
提问by Seth De
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?
我有一个 Oracle 数据库，我想将数据导出到一个文件。但是文件名、扩展名和分隔符将从表中获取值。问题是我不能使用表中的值。你能给我建议一种方法吗？或者如果我可以用批处理来做到这一点？
Table(id, path, filename, extension, separator)
表（id、路径、文件名、扩展名、分隔符）
script.sql
脚本.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
问候
回答by Alex Poole
You could use substitution variables and the new_valueclauseof the columncommand.
您可以使用替换变量，并且该new_value条款的的column命令。
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;
回答by Aleksej
SPOOL is a SQLPlus command, so you can not use it in a PlSQL block dynamically.
SPOOL 是 SQLPlus 命令，因此您不能在 PlSQL 块中动态使用它。
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:
d:\文件名.txt:
1| 2| 3
----------|----------|----------
1| 2| 3
