How to eliminate sql statement from spool output text file?

I wrote this script to export data from Oracle but I am running into and issue where the data export txt file includes the sql statement and spool off in the file along with the data. Can you provide suggestions on how to eliminate this from the output file? Thanks.

[The script to spool the data from oracle in sql*plus:

set echo off
set term off
set feedback off
set linesize 500
set pagesize 0
set sqlprompt ''
set trimspool on

--tbl_name
spool /filepath/tbl_name.txt
select columnA ||':;:'|| columnB ||':;:'|| to_char(columnC, 'yyyy-mm-dd hh24:mi:ss')
from db_name.tbl_name;
spool off




The output file:

select columnA ||':;:'|| columnB ||':;:'|| to_char(columnC, 'yyyy-mm-dd hh24:mi:ss')
from db_name.tbl_name;

1:;:XYZ:;:2008-11-19 11:29:48
2:;:ABC:;:2008-11-19 11:29:48
3:;:LMNOP:;:2010-06-09 10:36:28

spool off

cassie5643Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
I'm unable to reproduce the problem you describe.

Granted I'm on Windows but as long as you aren't using a 'here' script, it shouldn't matter.

Below is the complete example from my run (I left the sqlprompt on.  If done properly, you don't need to turn it off).
C:\>type a.sql
set echo off
set term off
set feedback off
set linesize 500
set pagesize 0
--set sqlprompt ''
set trimspool on

--tbl_name
spool l
select sysdate from dual;
exit;
spool off

C:\>sqlplus scott/tiger @a.sql

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Mar 4 13:33:41 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Oracle Label Security, OLAP and Data Mining Scoring Engine options

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Pr
oduction
With the Oracle Label Security, OLAP and Data Mining Scoring Engine options

C:\>type l.lst
04-MAR-11

C:\>

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Are you copying and pasting that 'script' into sqlplus?

try saving the script to say, myScript.sql and executing it with sqlplus:
sqlplus user/password @myscript.sql
0
 
cassie5643Author Commented:
no I'm executing the sql script using a cronjob using the syntax you mentioned
0
 
gopiseraCommented:

For me it went well.

Can you try once again  what slightwv said.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.