Clear the heading in Oracle spool

oracle sqlplus spool
Srinivasan Thirunavukkarasu picture Srinivasan Thirunavukkarasu · Jan 20, 2010 · Viewed 55.9k times · Source

I have spooled a file before running the below command and got the output like this,

I have set heading off, feedback off

SET HEADING OFF
SET FEEDBACK OFF
SPOOL D:\TEST.TXT
SELECT SYSDATE FROM DUAL;
SPOOL OFF

OUTPUT in TEST.TXT:

SQL> SELECT SYSDATE FROM DUAL;

20-JAN-09

SQL> SPOOL OFF

How can i remove the two SQL> lines. I want only the output.

Thanks in advance.

Answer

Tony Andrews picture Tony Andrews · Jan 20, 2010

The command you need is:

SET ECHO OFF

However, it only works for code run from scripts, not command entered interactively. You would create a script file like this (e.g. called test.sql):

SET HEADING OFF FEEDBACK OFF ECHO OFF PAGESIZE 0
SPOOL D:\TEST.TXT 
SELECT SYSDATE FROM DUAL; 
SPOOL OFF

Then in SQL Plus run it like this:

SQL> @test

I added PAGESIZE 0 to the SET command to remove the blank line you otherwise get before the date in the output file.

Related questions

Remove Column Header into the Output Text file

I want to create a flat file (text file) of my query from Oracle SQL Developer. I have successfully created the text file using SPOOL, thru a script text file, but i want to remove the header of each column …

Read More
Header formatting while spooling a csv file in sqlplus

I am required to spool a csv from a table in Oracle, using sqlplus. Following is the format required: "HOST_SITE_TX_ID","SITE_ID","SITETX_TX_ID","SITETX_HELP_ID" "664436565","16","2195301","0" "664700792","52","1099970","0" Following is the relevant piece of the shell script …

Read More
Append to spool file Oracle

I have one script file called Test.sql in D:\Scripts folder and the content of the file is given below SET SERVEROUTPUT ON SET DEFINE OFF SPOOL Test.log; SELECT USER_NAME FROM TUP_USER WHERE USER_ID=1432; SPOOL …

Read More