Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

How to eliminate sql statement from spool output text file?

Posted on 2011-03-04
4
Medium Priority
?
856 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 78

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 78

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

783 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