Solved

How to eliminate sql statement from spool output text file?

Posted on 2011-03-04
4
851 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 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 250 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 250 total points
ID: 35038893

For me it went well.

Can you try once again  what slightwv said.

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

679 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