Re: Spool in SQL*Plus without SQL statments

On Jun 22, 3:13 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

In regards to the '\' solution. It helps in reducing the amount of text but I'll just end up getting a
SQL>\
at the top of my file.
and of course the
SQL>spool off
at the bottom.

I can't have this. On top of this, I didn't have this issue until today. Received on Fri Jun 22 2007 - 14:32:55 CDT