Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Spool in SQL*Plus without SQL statments

Re: Spool in SQL*Plus without SQL statments

From: <fitzjarrell_at_cox.net>
Date: Fri, 22 Jun 2007 12:13:52 -0700
Message-ID: <1182539632.379635.41520@j4g2000prf.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US