Re: Spool in SQL*Plus without SQL statments

On Jun 22, 4:00 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jun 22, 2:32 pm, jreiner..._at_gmail.com wrote:
>
>
>
>
>
> > 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.- Hide quoted text -
>
> > - Show quoted text -
>
> Then what changed? Obviously something did, and either you're not
> reporting it here or you don't know exactly what was altered. You
> need to discover what may have been modified.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

Nothing changed in my code. The only difference was I was doing all the code I wrote above in SQL*Plus as opposed to saving it in a .sql file and running that file through SQL*Plus. I didn't 'report' this because I would never imagine it to make a difference.

Someone, without explaination, in another thread I searched suggested to run it through a file. This has helped me somewhat (but now I can't get the column headers to appear). Received on Fri Jun 22 2007 - 15:29:23 CDT