Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to eliminate sql statement from spool output text file?

Posted on 2011-03-04
4
Medium Priority
?
855 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:cassie5643
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35038687
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
 

Author Comment

by:cassie5643
ID: 35038764
no I'm executing the sql script using a cronjob using the syntax you mentioned
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 35038862
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
 
LVL 3

Assisted Solution

by:gopisera
gopisera earned 1000 total points
ID: 35038893

For me it went well.

Can you try once again  what slightwv said.

0

Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question