CODEWITHSUNDEEP
sqloracle-databaseshell
Faizers
Faizers

Reputation: 38

SQL header in result file

I have a SQL script:

SET HEADING OFF
SET ECHO OFF
SET FEEDBACK OFF
SET NEWPAGE NONE
SET PAGESIZE 0
SET TRUNCATE ON
SET VERIFY OFF

select
  count(case when msg_id like 'R%' and corr_id like 'F%' then 1 end) as Fid_Incoming ,
  count(case when msg_id like 'E%' and corr_id like 'F%' then 1 end) as Fid_Outgoing,
  count(case when msg_id like 'R%' and corr_id like 'E%' then 1 end ) as Equi_Incoming ,
  count(case when msg_id like 'E%' and corr_id like 'E%' then 1 end) as Equi_Outgoing
 from tb_msg
 where soc_id = 'WINDOWS'
 and msg_date >= trunc(sysdate);
 quit

That returns the following if called from sqlplus:

FID_INCOMING FID_OUTGOING EQUI_INCOMING EQUI_OUTGOING
------------ ------------ ------------- -------------
       0            0             0             0

But if I call the SQL from a shell script:

 sqlplus -s /@$TWO_TASK @${SQLDIR}/SWID_msgChecks.sql 1>${OUTPUT} 2>$1

The column names are not written to file, how do I get the headers/column names written to file?

Thanks

Upvotes: 0

Views: 619

Answers (1)

Mr. Llama
Mr. Llama

Reputation: 20899

You must not be calling it correctly from SQL*Plus because you've specifically SET HEADING OFF and SET PAGESIZE 0 which disabled the column headers.

From this reference document:

HEA[DING] {OFF|ON}
   print column headings

PAGES[IZE] {14|n}
   The height of the page - number of lines.
   0 will suppress all headings, page breaks, titles

When you're calling SQL*Plus from the shell script, you're getting exactly what you've specified that you want (i.e. no headings). If you want the headings to stay, you should use SET HEADING ON and set PAGESIZE to some non-zero value.

Upvotes: 1

Related Questions