Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

create flat file comma sep val csv from sql plus with field headers

21914Nov 24 2004 — edited Nov 29 2004
I am trying to create dumps of tables as comma seperated values. I have it working except for getting the header at the top of the file.
So more or less I want some results like:
*********************
"Name","Deptno","EmpNo"
"Smith","9999","12345"
"Jones","9998","12355"
"Homer","9997","12333"
also note I am enclosing all values in quotes.

Once I try to make changes to include the header things start going bad. One problem is I have over 200,000 records in several tables so with the 50000 pagesize limit I set it to 0. This causes a problem because it does not put headings when set to 0.
Here is a sample of current sql plus code.
any help will be appreciated.
Also note, I tried using the "colsep" method, but it left spaces in between fields which I find unacceptable.

***********************************
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 500
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
set serveroutput on
SET VERIFY OFF
SET HEADING on
set sqlprompt ''
set trimspool on
SET MARKUP HTML OFF SPOOL OFF
set termout off
spool tryloc.txt
SELECT '"'||l.API ||'","'||
l.CNTYNM||'","'||
l.CNTY||'","'||
l.PRMT||'","'||
l.TXDSTNM||'","'||
l.TXDST||'","'||
l.QUADNM||'","'||
l.SQUADNM||'","'||
l.SQUAD||'","'||
l.SLATD||'","'||
l.SLATM||'","'||
l.SMIS||'","'||
l.SLOND||'","'||
l.SLONM||'","'||
l.SMIW||'","'||
l.LATDD||'","'||
l.LONDD||'","'||
l.UTME||'","'||
l.UTMN||'","'||
l.LFLGTR||'","'||
l.LFLG||'","'||
l.LCOMMENT||'","'||
l.LDATE||'"'
FROM OGUSER.LOC_LKUP_VIEW l
where l.prmt<5;
spool off
set trimspool off
set termout on
set heading on
set sqlprompt 'johns sql >'
set feedback on
set serveroutput off
set echo on
set verify on

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 27 2004
Added on Nov 24 2004
2 comments
429 views