• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 498
  • Last Modified:

Using Spool to create a .csv

I'm running the following in sqlplus to create a .csv:
set echo off
set verify off
set termout off
set heading off
set pages 50000
set feedback off
set newpage none
set linesize 200

spool C:\test\QueryOutput.csv 
select 'personid'
from dual;
select to_char(personid) 
from vp_employeev42
Where personid < '500' and employmentstatus = 'Active';
spool off

Open in new window

The output is as follows:
SQL> select 'personid'
  2  ||','||'personnum'
  3  ||','||'personfullname'
  4  ||','||'lastnm'
  5  ||','||'firstnm'
  6  ||','||'employmentstatus'
  7  ||','||'companyhiredtm'
  8  from dual;
SQL> select to_char(personid)
  2    ||','||to_char(personnum)
  3    ||','||rtrim(lastnm)
  4    ||','||rtrim(firstnm)
  5    ||','||to_char(employmentstatus)
  6    ||','||to_char(companyhiredtm,'dd-mon-yyyy')
  7  from vp_employeev42
  8  Where personid < '500' and employmentstatus = 'Active';

SQL> spool off

Open in new window

The problem is the select statements are being displayed in the output which I do not want.  The last line "SQL> spool off" is also being displayed which I don't want.  Is there a way to only output the result set from the select statements in the .csv file?
1 Solution
slightwv (䄆 Netminder) Commented:
Do noy copy/paste the command.  Place them in a file with a .sql extension.

above the spool add:
set echo off
set trimspool on
set pages 0
set feedb off

select ...

Then from the sqlplsu prompt:  @whateverfile
algomaAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now