Re: Spool in SQL*Plus without SQL statments

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 Received on Fri Jun 22 2007 - 15:00:19 CDT