oracle-databaseoracle-sqldevelopersqlplusspoolcolumnheader

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 into my output.

I am getting this output:

Header000001 Header000002
------------ ------------
Adetail1     Bdetail1
Adetail2     Bdetail2
Adetail3     Bdetail3

But, I want to get this output:

Adetail1Bdetail1
Adetail2Bdetail2
Adetail3Bdetail3

I already tried the command "set heading off", but a message says:

"SQLPLUS COMMAND Skipped: set heading off".

These are the inputs I've issued:

spool on; 
spool C:\SQLFiles\PSB_ATMLKP.txt; 
set newpage 0; 
set echo off; 
set feedback off; 
set heading off; 

select terminal_number, terminal_name from terminal_table; 

spool off;

Solution

  • SQLPLUS COMMAND Skipped: set heading off

    That message is most likely because you are not executing it through SQL*Plus, but some GUI based tool. You are using SQLPlus command in SQL Developer. Not all SQL*Plus commands are guaranteed to work with SQL Developer.

    I would suggest you execute the script in SQLPlus and you would see no issues.

    You need:

    SET HEADING OFF
    

    This will not include the column headers in the output.

    Alternatively, you could also do this:

    SET PAGESIZE 0
    

    Using SQL Developer Version 3.2.20.10:

    spool ON
    spool D:\test.txt
    SET heading OFF
    SELECT ename FROM emp;
    spool off
    

    enter image description here

    Spool file got created with no issues:

    > set heading OFF
    > SELECT ename FROM emp
    SMITH      
    ALLEN      
    WARD       
    JONES      
    MARTIN     
    BLAKE      
    CLARK      
    SCOTT      
    KING       
    TURNER     
    ADAMS      
    JAMES      
    FORD       
    MILLER     
    
     14 rows selected