oracle 如何从sqlplus中的表中选择spool的文件名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5022130/
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
How to select spool's filename from table in sqlplus
提问by Fernando Rybka
how could I select the name of my spool filename from a table in sqlplus?
我怎样才能从 sqlplus 的表中选择我的假脱机文件名的名称？
I have something like:
我有类似的东西：
SPOOL ON
SPOOL XXXXXXXXX.bkp
SELECT * FROM my_table WHERE field1='value';
SPOOL OFF
And want the XXXXXXXXX.bkp to have the value of a field from this same table.
并希望 XXXXXXXXX.bkp 具有来自同一个表的字段的值。
Regards, Fernando
问候， 费尔南多
回答by Rajesh Chamarthi
You can use the SQLPLUS "new_value" feature to save the column value into a sqlplus variable and then spool it as below.
您可以使用 SQLPLUS“new_value”功能将列值保存到 sqlplus 变量中，然后按如下方式将其假脱机。
SQL> create table spool_file (
2 id number,
3 file_name varchar2(200)
4 );
Table created.
SQL> insert into spool_file values (1, 'test_spool_123.txt');
1 row created.
SQL> commit;
Commit complete.
SQL> column file_name new_value file_name;
SQL> select file_name from spool_file
2 where id = 1;
FILE_NAME
--------------------------------------------------------------------------------
test_spool_123.txt
SQL> spool '&file_name';
SQL> select object_id from dba_objects
2 where rownum < 10;
OBJECT_ID
----------
28
49
11
6
3
52
40
19
59
9 rows selected.
SQL> spool off;
And here are the contents from my spool file.
这是我的假脱机文件中的内容。
SQL> select object_id from dba_objects
2 where rownum < 10;
OBJECT_ID
----------
28
49
11
6
3
52
40
19
59
9 rows selected.
SQL> spool off;
You might also want to look at the UTL_FILEPackage and its subprograms if you need greater control over your writing to files.
如果您需要更好地控制对文件的写入，您可能还想查看UTL_FILE包及其子程序。
回答by FrustratedWithFormsDesigner
I'm not 100% sure what you're trying to do, but if you want a variable filename in your spool file you can do this:
我不是 100% 确定您要做什么，但是如果您想在假脱机文件中使用可变文件名，您可以这样做：
col dat1 NEW_VALUE v_start_time
select to_char(sysdate,'YYYY-MM-DD_HH24MISS') dat1 from dual;
spool test_script_&v_start_time..txt
/*
other stuff...
*/
spool off
This was an example taken from a script I have that adds a timestamp to the file name to make multiple output files easier to manage. Note the ..in the spool [filename]command.
这是从我的脚本中获取的一个示例，该脚本为文件名添加了时间戳，以使多个输出文件更易于管理。请注意，..在spool [filename]命令。
Since you say you want an actual field name in the filename, you will have to change my query to something that queries against the Oracle table that stores all the field names (I can't remember its name right now), but that should be pretty easy.
既然你说你想要文件名中的实际字段名称，你必须将我的查询更改为查询存储所有字段名称的 Oracle 表（我现在不记得它的名称），但这应该是挺容易。
