Re: Spool in SQL*Plus without SQL statments
- From: jreinert13@xxxxxxxxx
- Date: Fri, 22 Jun 2007 13:49:08 -0700
On Jun 22, 4:35 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:
On Jun 22, 3:29 pm, jreiner...@xxxxxxxxx wrote:
On Jun 22, 4:00 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:
On Jun 22, 2:32 pm, jreiner...@xxxxxxxxx wrote:
On Jun 22, 3:13 pm, "fitzjarr...@xxxxxxx" <fitzjarr...@xxxxxxx> wrote:
On Jun 22, 1:53 pm, jreiner...@xxxxxxxxx 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
.
- References:
- Spool in SQL*Plus without SQL statments
- From: jreinert13
- Re: Spool in SQL*Plus without SQL statments
- From: fitzjarrell@xxxxxxx
- Re: Spool in SQL*Plus without SQL statments
- From: jreinert13
- Re: Spool in SQL*Plus without SQL statments
- From: fitzjarrell@xxxxxxx
- Re: Spool in SQL*Plus without SQL statments
- From: jreinert13
- Re: Spool in SQL*Plus without SQL statments
- From: fitzjarrell@xxxxxxx
- Spool in SQL*Plus without SQL statments
- Prev by Date: Re: Spool in SQL*Plus without SQL statments
- Next by Date: RSA decryption in Oracle 9i
- Previous by thread: Re: Spool in SQL*Plus without SQL statments
- Next by thread: Re: Spool in SQL*Plus without SQL statments
- Index(es):
Relevant Pages
|
