UNIX script Fetch the SQL query output to a file

Question

I have a SQL script

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.

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

Not relevant to the question, but converting the dates to strings for comparison isn't efficient, particularly if there's an index on fh.access_date. You can get the same result with and fh.access_date between trunc(SYSDATE) and trunc(SYSDATE + 1). — Alex Poole
– Alex Poole, Commented Dec 29, 2010 at 15:55

Codo · Accepted Answer · 2010-12-29 17:39:19Z

1

It's probably easier to use the spool command:

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

answered Dec 29, 2010 at 17:39

Codo

79.7k18 gold badges176 silver badges218 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex Poole · Accepted Answer · 2010-12-29 15:29:37Z

0

You don't need the ; a the end of the connect statement; 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 sqlplus command - zero or 20?

You can change the WHENEVER SQLERROR to EXIT SQL.SQLCODE to get a better idea what went wrong - though it should be in the output anyway - but some shells don't understand big numbers.

answered Dec 29, 2010 at 15:29

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

1 Comment

DCookie Over a year ago

The semicolons are unnecessary but harmless.

Collectives™ on Stack Overflow

UNIX script Fetch the SQL query output to a file

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related