Re: Spool in SQL*Plus without SQL statments

On Jun 22, 4:35 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Jun 22, 3:29 pm, jreiner..._at_gmail.com wrote:
>
>
>
>
>
> > 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).- Hide quoted text -
>
> > - Show quoted text -
>
> If you'll note I also suggested you 'run it through a file' in this
> thread:
>
> "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 "
>
> You likely have pagesize set to 0 so even if you have headings enabled
> they won't display. Setting your pagesize to some large number
> (hopefully in excess of the rows returned by your query) will
> magically cause these headers to re-appear.
>
> David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -

I realize that, I just got distracted with the '\' between the spools as being the solution logic.

Yea thanks, I also read about how set pagesize 0 includes the heading removal on that the same thread I found in my search.

I'm still confused as to the reason running the file works and not otherwise but at least I can do my work now. thanks Received on Fri Jun 22 2007 - 15:49:08 CDT