Search code examples
oraclesqlplusspool

Hide SQL > statements in the spool file

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
Solution

  • 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.