@test.sql" and "SQL> spool off" in the file test.txt(spool file) my steps set heading off set pages 0 set trimspool on..." /> @test.sql" and "SQL> spool off" in the file test.txt(spool file) my steps set heading off set pages 0 set trimspool on..." /> @test.sql" and "SQL> spool off" in the file test.txt(spool file) my steps set heading off set pages 0 set trimspool on..."/>

Hide SQL > statements in the spool file

19.4k views Asked by At

I want to hide the "SQL> @test.sql" and "SQL> spool off" in the file test.txt(spool file)

my steps

set heading off 
set pages 0 
set trimspool on 
set lines 120 
set feedback off 
set echo off 
set termout off 
spool test.txt
@test.sql
spool off

test.txt

SQL> @test.sql                              
2002452199  2797    9/1/2014    9/30/2014   OO56128665          934 90087318    1   6046
10226342            ##########  0   0                   


SQL> spool off
2

There are 2 answers

0
Maheswara Raju.Varakavi On BEST ANSWER

reason for getting sql statements into results is:-

    If you copy those sql scripts and paste directly on sql prompt,and execute directly will gets the issues like this. So, to avoid this, place all the script code into one .sql file, and execute that entire file by giving @filename.sql in either "sql command prompt" or in "Dos prompt by logging into sql from Dos".

to avoid the above issue/problem do the following steps:-

--------------here is my spool_test.sql file code -------------

SET echo off
SET feedback off
SET term off
SET pagesize 0
SET linesize 200
SET newpage 0
SET space 0
col name format a120
spool C:\test.txt
@D:\mahesh-plsql-books\spool\test.sql
SPOOL OFF

1) If you execute the above .sql file within SQL> prompt, then it doesn not shows the sql statements along with results in your "test.txt". Like-->

SQL> @D:\mahesh-plsql-books\spool\spool_test2.sql

2) The another way to avoid the "SQL Statements" within the results of "test.txt", is executing the above .sql file with in Dos command prompt(c:>) instead of "sql prompt" like shown below--->

C:\>sqlplus scott/tiger @D:\mahesh-plsql-books\spool\spool_test.sql

......then your "test.txt" does not show the sql statements, as well as @test.sql.

note:- scott/tiger is my oracle's username/password

Thanks and Regards,
V.Maheswara Raju.

1
doberkofler On

This example script (test.sql):

set heading off verify off feedback off echo off term off pagesize 0 linesize 10000 trimout on trimspool on timing off
spool test.log
SELECT 'content of spool file' FROM dual;
spool off

when run:

>sqlplus scott/tiger
SQL*Plus: Release 12.1.0.1.0 Production on Mon Jun 22 08:46:58 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Last Successful login time: Mon Jun 22 2015 08:46:52 +02:00
Connected to:
Oracle Database 12c Release 12.1.0.1.0 - 64bit Production
SQL> @test.sql
SQL> exit
Disconnected from Oracle Database 12c Release 12.1.0.1.0 - 64bit Production

generates a "clean" spool file:

>type test.log
content of spool file