Extract records from Oracle to UNIX file with headers

Shell Programming and Scripting
1

Hi,

I have a shell script which extracts records form oracle to unix file.

 
sqlplus -s ${WMD_DM_CONNECT} <<EOF >$tmpfile
set heading off
set pagesize 0
set feedback off
select CD_DESC||'|'||CD_ID||'|'||'Arun'||'|'||'Montu'  from WMD_SYS_CD_LKUP
where CD_TYP = 'FILE_VLDTN_ERROR_CD';
exit
EOF

I want the headers also in the file. That too pipe delimited. Is there a way?

Any help appreciated..
Thanks in advance

2

I guess set heading on would print the headers. You know you can set the field/column separator to | , so you don't need that intricate select statement?

3

Yes i used the below query to get output:

sqlplus -s ${WMD_DM_CONNECT} <<EOF>arun_temp.out
set feedback off
set colsep |
select proc_id ,FILE_ID,FILE_DT,STAT_CD from wmd_file_cntl where rownum < 10;
exit
EOF

 
   PROC_ID|   FILE_ID|FILE_DT  |   STAT_CD
----------|----------|---------|----------
         2|         1|24-NOV-12|         3
         3|         1|24-NOV-12|         3
         4|         1|24-NOV-12|         3
         5|         1|24-NOV-12|         3
         6|         1|24-NOV-12|         3
         7|         1|24-NOV-12|         3
         8|         1|24-NOV-12|         2
         9|         1|24-NOV-12|         3
        18|         1|24-NOV-12|         2

But the data doesnt look good to me as pipe delimited data should be like the blow:

 
PROC_ID|FILE_ID|FILE_DT|STAT_CD
2|1|24-NOV-12|3
3|1|24-NOV-12|3
4|1|24-NOV-12|3
5|1|24-NOV-12|3
6|1|24-NOV-12|3
7|1|24-NOV-12|3
8|1|24-NOV-12|2
9|1|24-NOV-12|3
8|1|24-NOV-12|2

Can you suggest what i am doing wrong here?

4

If I recall correctly - forgive me, my Oracle experience is a bit rusty - you can also set an indentation or alignment option ? If not, you need to remove the whitespace with awk or sed .

6

One ca set the column format however it might again endup with wrong headers

Ex:

 
column a format a20
column b format a20

I guess still the concat option holds good. if one want header input a dummy header selection from dual table before actual select staement and keep header off.

7

try this

sqlplus -s ${WMD_DM_CONNECT} <<EOF >$tmpfile set heading off set pagesize 0 set feedback off
dbms_output.put_line('EMPLID'||'|'||'EMPL_RCD'||'|'||'Arun'||'|'||'Mountu');
select CD_DESC||'|'||CD_ID||'|'||'Arun'||'|'||'Montu'  from WMD_SYS_CD_LKUP where CD_TYP = 'FILE_VLDTN_ERROR_CD'; exit EOF