| pipe delimmited file without sql inside [message #431320] |
Mon, 16 November 2009 16:06  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
Hi,
I am running query on Oracle 9i R2 on Sun Solaris to create pipe (|) delimmited text file but its pulling sql statements into extract.
How can i avoid this?
set linesize 4000 pagesize 0 trimspool on feedback off verify off spool on
set define off
spool Course.txt
select b.S_name ||'|'|| a.c_code ||'|'|| a.c_name ||'|'|| a.c_wave
from MAP c, COURSE a, STAKE b
where a.C_ID = c.C_ID and
b.C_ST_ID = c.C_ST_ID;
spool off;
Please let me know.
Thanks for your help!
|
|
|
|
| Re: pipe delimmited file without sql inside [message #431321 is a reply to message #431320] |
Mon, 16 November 2009 16:16  |
poratips
Messages: 345
Registered: April 2005
Location: IL
|
Senior Member |
|
|
I forgot to post result which its pulling:
SQLP>sselect b.S_name ||'|'|| a.c_code ||'|'|| a.c_name ||'|'|| a.c_wave
2 from MAP c, COURSE a, STAKE b
3 where a.C_ID = c.C_ID and
4 b.C_ST_ID = c.C_ST_ID;
FIN:Acc|SAP1|Project Overview|WV1
FIN:Acc|SAP2|Project Overview Assessment|WV2
I don't want sql in my text file, just data.
Thanks.
Poratips
|
|
|
|
| Re: pipe delimmited file without sql inside [message #431323 is a reply to message #431321] |
Mon, 16 November 2009 17:27 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Works for me. If you really did run what you did post then the result would be OK, but you would have gotten an additional 'SP2-0158: unknown SET option "spool" ' error.
oracle log $ cat test.sql
set linesize 4000 pagesize 0 trimspool on feedback off verify off spool on
set define off
spool Course.txt
select 1 from dual;
spool off;
exit
oracle log $ sqlplus user/pass@sid @test.sql
SQL*Plus: Release 10.2.0.4.0 - Production on Di Nov 17 00:25:19 2009
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SP2-0158: unknown SET option "spool"
1
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle log $ cat Course.txt
1
oracle log $
|
|
|
|
| Re: pipe delimmited file without sql inside [message #431334 is a reply to message #431323] |
Mon, 16 November 2009 22:35 |
sajith741
Messages: 11
Registered: November 2009
Location: Singapore
|
Junior Member |
|
|
Please use sqlplus -s option and you could use colsep for setting column separator other than space.
sqlplus -s scott/scott123
SET ECHO OFF
SET TERMOUT OFF
SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET PAGESIZE 50000
SET LINESIZE 32767
SET TRIMSPOOL ON
SET COLSEP |
SPOOL "C:\DATA\TEXT.TXT"
SELECT * FROM ACQ_TREE WHERE ROWNUM < 3;
SPOOL OFF
Result of the query:
ACQ_ID |ACQ_TYPE| ACQ_LEVEL|BILLING_LEVEL
--------|--------|----------|-------------|
10000022|500404 | 1| 1|
[Updated on: Mon, 16 November 2009 22:36] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: pipe delimmited file without sql inside [message #433833 is a reply to message #431323] |
Fri, 04 December 2009 07:59 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
ThomasG wrote on Mon, 16 November 2009 18:27Works for me. If you really did run what you did post then the result would be OK, but you would have gotten an additional 'SP2-0158: unknown SET option "spool" ' error.
You would also get an error something like:
unknown command beginning "sselect...
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
