Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Spool in SQL*Plus without SQL statments
On Jun 22, 1:53 pm, jreiner..._at_gmail.com wrote:
> I'm having the most fustrating problem right now.
>
> I'm trying to spool output of a select statement to a csv file.
> Originally I had it spooling to a text file with my settings and it
> was fine.
> In the midst of trying to accomidate a .csv file, I have lost ability
> to remove the sql statements from my output. Despite the fact I'm
> pretty sure the I put the same settings as before.
>
> I thought set echo off accomplished this but now I'm just completely
> lost and fustrated (I don't even know what it does in SQP*Plus
> anymore..setting it on and off does nothing anymore).
> every search attempt I've made has been futile (which rarely
> happens...)
>
> My code looks like this:
>
> set linesize 1000
> set heading on
> set feedback off
> set echo off
> set trimspool off
> set colsep ','
> set termout off
> set newpage none
> spool C:\output.csv
> select pet_priceid as Price, location
> from pet_price
> where pet_priceid > 897800;
> spool off
>
> My results look like this:
>
> SQL> select petroleum_priceid as Price updateby
> 2 from petroleum_price
> 3 where petroleum_priceid > 897800;
> PRICE
> UPDATEBY
>
> ---------- -------------------------
> 897801 Mike Rau
> .........
>
> That above 'SQL> select...' prompt is what I can't for the life of me
> get rid of. The worst part, is initially this was an easy issue to
> solve.
>
> ALSO,
> If anyone can help me get rid of the '-----' below the table headings
> that would be great. This was my next task but obvously never got to
> it.
Put this in a file and run it from the SQL> prompt with the @<scriptname> syntax:
set linesize 1000
set heading on
set feedback off
set echo off
set trimspool off
set colsep ','
set termout off
set newpage none
select pet_priceid as Price, location
from pet_price
where pet_priceid > 897800
spool C:\output.csv
/
spool off
For example, you save the above text in a file named myqry.sql. At the SQL*Plus prompt you would:
SQL> @myqry
SQL> exit
You now have a 'sort of' csv file,of your query output, which includes headings (so, I'm a snob and won't consider any file with headings a CSV file). If you really want a csv file:
set linesize 1000
set heading off
set feedback off
set echo off
set trimspool off
set colsep ','
set termout off
set newpage none
select pet_priceid,''"||location||'"'
from pet_price
where pet_priceid > 897800
spool C:\output.csv
/
spool off
You'll end up with an actual csv file (in my opinion), with your text surrounded in "" and without any cluttered headings. (Flame away, all who feel inclined. I have my opinions.)
As far as I know the only way you can rid yourself of the dreaded pseudo-underline is to edit the resulting text file with sed, awk, or vi.
David Fitzjarrell Received on Fri Jun 22 2007 - 14:13:52 CDT