How to get sqlplus column header once in csv file?

Shell Programming and Scripting
1

Hi All,

Could anyoone please let me know how do I get sqlplus column header once in csv file

Scripts are below:

cat concreq.sh

#!/bin/bash

. $HOME/.profile

while [ 1 -eq 1 ]; do
sqlplus apps/pwd <<-EOF
set lines 100 pages 100
col "USER_CONCURRENT_QUEUE_NAME" format a40;
--set termout off
--set arraysize 5
set echo off
set verify off
set heading on
set feed off
set colsep,;
@/test1/concreq.sql
EOF
sleep 15
done
$

$cat concreq1.sql

column tm new_value file_time noprint
select to_char(sysdate, 'MMDDYYYY') tm from dual ;
prompt &file_time
spool /test1/TEST1_CM_&file_time..csv append
col running head "Running" format 9999999
select to_char(r.ACTUAL_START_DATE,'MM-DD-YYYY HH24:MI:SS'),sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,sum(decode(r.phase_code,'P',1,0)) pending
from applsys.fnd_concurrent_requests r,applsys.fnd_concurrent_processes p,applsys.fnd_concurrent_queues q
Where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes >= 0 and (r.phase_code in ('R','P','I'))
group by r.ACTUAL_START_DATE
UNION ALL
SELECT (to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')),0,0 from dual
WHERE  NOT EXISTS
(select to_char(r.ACTUAL_START_DATE,'MM-DD-YYYY HH24:MI:SS'),sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,sum(decode(r.phase_code,'P',1,0)) pending
from applsys.fnd_concurrent_requests r,applsys.fnd_concurrent_processes p,applsys.fnd_concurrent_queues q
Where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes >= 0 and (r.phase_code in ('R','P','I'))
group by r.ACTUAL_START_DATE);
spool off;
-bash-3.2$

Current output after executing the concreq.sh script:


-bash-3.2$ cat TEST1_CM_04102013.csv

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0

$

Expected output should be:

-bash-3.2$ cat TEST1_CM_04102013.csv

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0
04-16-2013 19:03:32,       1,         0
04-16-2013 19:03:32,       1,         0
$

Thanks for your time!

Regards,
a1_win

2

either set no header option and add the headers later or better
a) capture the header
b) remove all the headers, additional separator lines like "--------"
c) then add the captured header information

3

Set PAGESIZE option to higher number or set it to 0 if you want to suppress heading and page breaks.

4

Can you this options..

 set termout off
set feedback off
set head on
set verify off
set echo off
set linesize 30000
set pagesize 0
set trimspool on

---------- Post updated at 07:39 AM ---------- Previous update was at 07:05 AM ----------

Just now i tried it's working for me. Try and let me know.

set termout off
set feedback off
set head on
set verify off
set echo off
set linesize 30000
set pagesize 0
set trimspool on
column tm new_value file_time noprint
select to_char(sysdate, 'MMDDYYYY') tm from dual ;
prompt &file_time
spool /home/oracle/TEST1_CM_&file_time.csv append
col running head "Running" format 9999999
select to_char(r.hiredate,'MM-DD-YYYY HH24:MI:SS') hiredate from emp;
spool off;
5

Hi, this is a a similar solution of matrixmadhan:

cat spool.txt

         X          Y          Z
---------- ---------- ----------
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3

         X          Y          Z
---------- ---------- ----------
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3


cat spool.txt| grep -v "^.$"| head -2 > header.txt
cat header.txt > final_spool.txt
grep -v -f header.txt spool.txt | grep -v "^.$" >>final_spool.txt

cat final_spool.txt
        X          Y          Z
---------- ---------- ----------
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3
         1          2          3

Try also without dots:

cat spool.txt| grep -v "^$"| head -2 > header.txt
cat header.txt > final_spool.txt
grep -v -f header.txt spool.txt | grep -v "^$" >>final_spool.txt
6

Thanks to everyone who took out time to share the resolution.
I will try with the suggestions provided and update with the results shortly.

Thanks to all for your time and attention!

A great learning unix forum!!!

Regards.
a1_win

---------- Post updated at 12:26 PM ---------- Previous update was at 11:22 AM ----------

I get the output as expected by using the above steps as suggested by unix forum experts in this post:

-bash-3.2$ cat final_Tst_CM_04172013.csv

TO_CHAR(R.ACTUAL_ST,   RUNNING,   PENDING
-------------------,----------,----------
04-17-2013 10:56:48,         1,         0
04-17-2013 10:56:48,         1,         0
04-17-2013 10:56:48,         1,         0
04-17-2013 10:56:48,         1,         0
04-17-2013 10:56:48,         1,         0
04-17-2013 10:56:48,         1,         0
04-17-2013 10:56:48,         1,         0
04-17-2013 10:56:48,         1,         0
04-17-2013 10:56:48,         1,         0
04-17-2013 10:56:48,         1,         0

Thanks,
a1_win

1 Like