Spool to CSV to include column names

[rogerte]

Newby to Oracle (and SQL) so please excuse simple question.

We have a sql script that exports data from joined tables to a CSV file, no problem

basically

SPOOL exportfile.csv

select ca.column1||','||ca.column2||','|| va column1
from table1 ca, table2 va
where ca.opid = va.opid

SPOOL off

what we want to do is add column names at the beginning:
colname1,colname2,colname3

I have tried adding a select 'colname1,colname2,colname3' from dual statement before the actual select, but all that appears is the colname text, not the data.

Can anyone tell me what I am doing wrong?

Thanks folks

Rog

 

[SantaMufasa]

Rog,

To produce what you want, the script should look like this:

set pagesize 0
set trimspool on
set feedback off
set echo off
spool <your output file name>
select 'colname1,colname2,colname3' from dual;
select ca.column1||','||ca.column2||','|| va column1
from table1 ca, table2 va
where ca.opid = va.opid;
spool off

Let us know whether or not this meets your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 17:08 (26Jan05) UTC (aka "GMT" and "Zulu"),
@ 10:08 (26Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[rogerte]

Mufasa

Many thanks.

I was basically correct (I had forgotten to include the semi-colon in my example), however my problem turned out that I had previously used SQL Navigator to change some records, and the commits hadn't worked correctly - so it couldn't find the records I was expecting it to find!