On Jun 22, 3:32 pm, jreiner...@gmail.com wrote:
> On Jun 22, 3:13 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
>
>
>
>
> > On Jun 22, 1:53 pm, jreiner...@gmail.com wrote:
>
> > > I'm having the most fustrating problem right now.
>
> > > I'm trying tospooloutput of a selectstatementto 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
> > >spoolC:\output.csv
> > > select pet_priceid as Price, location
> > > from pet_price
> > > where pet_priceid > 897800;
> > >spooloff
>
> > > 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
>
> >spoolC:\output.csv
> > /
> >spooloff
>
> > 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
>
> >spoolC:\output.csv
> > /
> >spooloff
>
> > 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>spooloff
> 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 -

I've been told that the 'termout' set to OFF I used was the solution
twice now....I'm sure I only had echo off before and it was fine but
whatever...I've still added this setting in my endless search.

Can anyone answer why set termout off is doing nothing?