Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, beni.

Asked: January 25, 2018 - 6:08 am UTC

Last updated: April 20, 2018 - 2:52 am UTC

Version: Oracle 12c

Viewed 1000+ times

You Asked

I have a sql query that needs to spool a select statement in a csv file.
Here is the query:

set colsep '|'
set echo off
set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''
set trimspool on
set headsep off

Spool 'sample.csv'

select * from Table;

spool off
exit ;
-------------


However, when I view the csv output, the output contains the sql statement like below and it doesn't contain the headings. Please help, I can find the parameter to remove it.


and Connor said...

Just put that script into a file, then you wont see the SQL


SQL> host cat c:\temp\x.sql
set colsep '|'
set echo off
set feedback off
set linesize 1000
set pagesize 0
set sqlprompt ''
set trimspool on
set headsep off
Spool c:\temp\sample.csv
select * from scott.emp;
spool off

SQL> @c:\temp\x.sql
      7369|SMITH     |CLERK    |      7902|17-DEC-80|       800|          |        20
      7499|ALLEN     |SALESMAN |      7698|20-FEB-81|      1600|       300|        30
      7521|WARD      |SALESMAN |      7698|22-FEB-81|      1250|       500|        30
      7566|JONES     |MANAGER  |      7839|02-APR-81|      2975|          |        20
      7654|MARTIN    |SALESMAN |      7698|28-SEP-81|      1250|      1400|        30
      7698|BLAKE     |MANAGER  |      7839|01-MAY-81|      2850|          |        30
      7782|CLARK     |MANAGER  |      7839|09-JUN-81|      2450|          |        10
      7788|SCOTT     |ANALYST  |      7566|09-DEC-82|      3000|          |        20
      7839|KING      |PRESIDENT|          |17-NOV-81|      5000|          |        10
      7844|TURNER    |SALESMAN |      7698|08-SEP-81|      1500|          |        30
      7876|ADAMS     |CLERK    |      7788|12-JAN-83|      1100|          |        20
      7900|JAMES     |CLERK    |      7698|03-DEC-81|       950|          |        30
      7902|FORD      |ANALYST  |      7566|03-DEC-81|      3000|          |        20
      7934|MILLER    |CLERK    |      7782|23-JAN-82|      1300|          |        10
set sqlprompt 'SQL>'
SQL>host cat c:\temp\sample.csv
      7369|SMITH     |CLERK    |      7902|17-DEC-80|       800|          |        20
      7499|ALLEN     |SALESMAN |      7698|20-FEB-81|      1600|       300|        30
      7521|WARD      |SALESMAN |      7698|22-FEB-81|      1250|       500|        30
      7566|JONES     |MANAGER  |      7839|02-APR-81|      2975|          |        20
      7654|MARTIN    |SALESMAN |      7698|28-SEP-81|      1250|      1400|        30
      7698|BLAKE     |MANAGER  |      7839|01-MAY-81|      2850|          |        30
      7782|CLARK     |MANAGER  |      7839|09-JUN-81|      2450|          |        10
      7788|SCOTT     |ANALYST  |      7566|09-DEC-82|      3000|          |        20
      7839|KING      |PRESIDENT|          |17-NOV-81|      5000|          |        10
      7844|TURNER    |SALESMAN |      7698|08-SEP-81|      1500|          |        30
      7876|ADAMS     |CLERK    |      7788|12-JAN-83|      1100|          |        20
      7900|JAMES     |CLERK    |      7698|03-DEC-81|       950|          |        30
      7902|FORD      |ANALYST  |      7566|03-DEC-81|      3000|          |        20
      7934|MILLER    |CLERK    |      7782|23-JAN-82|      1300|          |        10

SQL>


If you are on 12.2, check out "set markup csv on" as well

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

feedback

beni cruz, April 19, 2018 - 5:36 am UTC

i did the same format but my output is full of space

sample output:

1
eProc
Acrobat
11
Standard

75124-0000025466


----
I need this output

1|eProc|Acrobat|11|Standard|75124-0000025466
Connor McDonald
April 20, 2018 - 2:52 am UTC

Complete test case please.

Missing Headings

AndyP, April 20, 2018 - 6:30 am UTC

and it doesn't contain the headings

set pagesize 0 suppresses the headings
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.