Solved

How to eliminate sql statement from spool output text file?

Posted on 2011-03-04
4
847 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
  • 2
4 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
no I'm executing the sql script using a cronjob using the syntax you mentioned
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
Comment Utility
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 250 total points
Comment Utility

For me it went well.

Can you try once again  what slightwv said.

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now