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.

On Jun 22, 1:53 pm, jreiner...@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

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 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.

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?

On Jun 22, 2: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 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

On Jun 22, 4:00 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> On Jun 22, 2: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 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).

On Jun 22, 3:29 pm, jreiner...@gmail.com wrote:
> On Jun 22, 4:00 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
>
>
>
>
> > On Jun 22, 2: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 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

On Jun 22, 4:35 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
> On Jun 22, 3:29 pm, jreiner...@gmail.com wrote:
>
>
>
>
>
> > On Jun 22, 4:00 pm, "fitzjarr...@cox.net" <fitzjarr...@cox.net> wrote:
>
> > > On Jun 22, 2: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 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

On Jun 22, 7:53 pm, jreiner...@gmail.com wrote:
> 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...)

I'm puzzled. Wasn't this clear enough?

http://download-uk.oracle.com/docs/c...0.htm#i2698923

SET ECHO {ON | OFF}

Controls whether or not to echo commands in a script that is executed
with @, @@ or START. ON displays the commands on screen. OFF
suppresses the display. ECHO does not affect the display of commands
you enter interactively or redirect to SQL*Plus from the operating
system.

On Jun 23, 12:03 pm, William Robertson <williamr2...@googlemail.com>
wrote:
> On Jun 22, 7:53 pm, jreiner...@gmail.com wrote:
>
> > 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...)
>
> I'm puzzled. Wasn't this clear enough?
>
> http://download-uk.oracle.com/docs/c...102/b14357/ch1...
>
> SET ECHO {ON | OFF}
>
> Controls whether or not to echo commands in a script that is executed
> with @, @@ or START. ON displays the commands on screen. OFF
> suppresses the display. ECHO does not affect the display of commands
> you enter interactively or redirect to SQL*Plus from the operating
> system.

You're puzzled? It isn't too hard to understand (for most people). I
thought turning echo 'off' worked like it does in every other command
line language...and in practice it seemed to be working that way.
Don't pretend that it isn't a little weird, from a programmers
perspective at least, it applies to the '@', '@@', START commands
only. Good specificity though, I will admit.

Hopefully the point of this post, other than attempting to ridicule
me, was to point out how the oracle documentation would have been
helpful. For that I apologize.



Oh and to get rid of the column heading underline I asked about
earlier its:

'set underline off'

oddly enough it wasn't in the setting lists I looked at (Looked on the
web; not oracle docs though).