oracle UNIX 脚本将 SQL 查询输出提取到文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4554839/
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
UNIX script Fetch the SQL query output to a file
提问by Capslockk
I have a SQL script
我有一个 SQL 脚本
select fh.box_name
from file_update_history fh,update_file_vw ff
WHERE
ff.file_id=fh.file_id
and fh.act_record_count IS NULL
AND FF.STATUS_CD <> 'OLD'
and to_char(fh.access_date,'YYYYMMDD') between to_char(SYSDATE, 'YYYYMMDD') and to_char(SYSDATE+1, 'YYYYMMDD')
order by fh.exp_arr_time_update_file,FF.FILE_NAME;
I need to get/spool the output of this SQL into a flat file. Which should have the query output. I am not getting the output of the query into the file.
我需要将此 SQL 的输出获取/假脱机到一个平面文件中。其中应该有查询输出。我没有将查询的输出放入文件中。
I have used
我用过了
sqlplus /NOLOG <<My_DB > $DB_OUTPUT_FILE
set feedback on;
set term on;
set echo on;
set heading off;
set underline off;
set pagesize 10000;
set linesize 999;
set wrap off;
WHENEVER SQLERROR EXIT 20;
connect ${My_DB_USER}/${My_DB_PASSWORD} ;
select fh.box_name
from file_update_history fh,update_file_vw ff
WHERE
ff.file_id=fh.file_id
and fh.act_record_count IS NULL
AND FF.STATUS_CD <> 'OLD'
and to_char(fh.access_date,'YYYYMMDD') between to_char(SYSDATE, 'YYYYMMDD') and to_char(SYSDATE+1, 'YYYYMMDD')
order by fh.exp_arr_time_update_file,FF.FILE_NAME;
exit
My_DB
回答by Codo
It's probably easier to use the spool command:
使用 spool 命令可能更容易：
sqlplus /NOLOG <<My_DB
set feedback on
set term on
set echo on
set heading off
set underline off
set pagesize 10000
set linesize 999
set wrap off
WHENEVER SQLERROR EXIT 20
connect ${My_DB_USER}/${My_DB_PASSWORD}
SET ECHO OFF
SET TERM OFF
SPOOL ${DB_OUTPUT_FILE}
select fh.box_name
from file_update_history fh,update_file_vw ff
WHERE
ff.file_id=fh.file_id
and fh.act_record_count IS NULL
AND FF.STATUS_CD <> 'OLD'
and to_char(fh.access_date,'YYYYMMDD') between to_char(SYSDATE, 'YYYYMMDD') and to_char(SYSDATE+1, 'YYYYMMDD')
order by fh.exp_arr_time_update_file,FF.FILE_NAME;
SET SPOOL OFF
exit
My_DB
回答by Alex Poole
You don't need the ;a the end of the connectstatement; can't check right now but is it possible that is causing an error, or that the credentials are wrong? Is the output file completely empty? And what is the return code from the sqlpluscommand - zero or 20?
你不需要;aconnect语句的结尾；现在无法检查，但是否有可能导致错误，或者凭据错误？输出文件是否完全为空？sqlplus命令的返回码是什么- 零或 20？
You can change the WHENEVER SQLERRORto EXIT SQL.SQLCODEto get a better idea what went wrong - though it should be in the output anyway - but some shells don't understand big numbers.
您可以更改WHENEVER SQLERRORtoEXIT SQL.SQLCODE以更好地了解出了什么问题-尽管无论如何它都应该在输出中-但是有些shell不理解大数字。
