Solved

Using Spool to create a .csv

Posted on 2012-03-19
2
475 Views
Last Modified: 2012-03-19
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'
||','||'personnum'
||','||'personfullname'
||','||'lastnm'
||','||'firstnm'
||','||'employmentstatus'
||','||'companyhiredtm' 
from dual;
select to_char(personid) 
  ||','||to_char(personnum)
  ||','||rtrim(lastnm)
  ||','||rtrim(firstnm)  
  ||','||to_char(employmentstatus)
  ||','||to_char(companyhiredtm,'dd-mon-yyyy')
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;
personid,personnum,personfullname,lastnm,firstnm,employmentstatus,companyhiredtm
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';
335,00012345,LastName1,FirstName1,Active,20-jan-1978
357,00012346,LastName2,FirstName2,Active,09-feb-1979

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?
0
Comment
Question by:algoma
2 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37738362
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
0
 

Author Closing Comment

by:algoma
ID: 37738413
beautiful
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now