Home » SQL & PL/SQL » SQL & PL/SQL » pipe delimmited file without sql inside (Oracle 9i R2 on Sun Solaris )
pipe delimmited file without sql inside [message #431320] Mon, 16 November 2009 16:06 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3185
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 Go to previous messageGo to next message
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 #431344 is a reply to message #431320] Mon, 16 November 2009 23:22 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Although it is hidden in some other posts, the one you are after is
set echo off
Re: pipe delimmited file without sql inside [message #433706 is a reply to message #431344] Thu, 03 December 2009 13:53 Go to previous messageGo to next message
poratips
Messages: 345
Registered: April 2005
Location: IL
Senior Member
Thsnk you everyone for your response and sorry about getting back as i was traveling and couldn't get back to you guys.
Really appreciate!

Regards,
Poratips
Re: pipe delimmited file without sql inside [message #433729 is a reply to message #431320] Thu, 03 December 2009 19:44 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
try this next time.

Kevin
Re: pipe delimmited file without sql inside [message #433833 is a reply to message #431323] Fri, 04 December 2009 07:59 Go to previous message
joy_division
Messages: 4616
Registered: February 2005
Location: East Coast USA
Senior Member
ThomasG wrote on Mon, 16 November 2009 18:27
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.


You would also get an error something like:
unknown command beginning "sselect...

Previous Topic: Update table runs too low. Please help
Next Topic: how to alter a package being called in a scheduled Job
Goto Forum:
  


Current Time: Tue Sep 27 23:01:06 CDT 2016

Total time taken to generate the page: 0.40343 seconds