Remove Column Header into the Output Text file

Question

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;

Subsidy

Could you give us all commands you've issued and in which order? – Honesty

<pre> 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; </pre> – Subsidy

I've edited them into your question for you. – Honesty

Answer 1

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

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 

Delmardelmer

Thanks for the reply. My version is 2.1.1.64. – Subsidy

Then perhaps you could use SQL*Plus rather than SQL Developer. Or upgrade your SQL Developer. Please mark it answered if it has helped you. – Delmardelmer

This doesn't answer the question. The poster asked how to do this using SQL Developer. – Statistics

@TerribleTadpole I think you haven't read complete answer. I have clearly shown how to do it in SQL Developer. OP has marked it as an answer too. – Delmardelmer

Answer 2

Add:

set underline off

to the beginning of the SQL script.

In my SQL scripts I have:

SET TERMOUT OFF
set colsep |
set pagesize 0 
set trimspool on
set pagesize  0 embedded on
SET heading on
SET UNDERLINE OFF
spool file_path
-- your SQL here
spool off

See this book for reference.

Neckband

useful if you want headlines, but no underline. Otherwise, if you prefer just results, setting heading off will suffice. That is: 'SET heading OFF' – Enlistee

Answer 3

A full exmple. The trick is sqlplus -s

 sqlplus -s / as sysdba <<EOF
> SET TERMOUT OFF
> SET LINESIZE 1000
>  set echo off;
> set feedback off;
> set heading off;
> spool /tmp/killSession${esquema}.sql
> select 'alter system kill session'||' ''' || s.sid||','||s.serial# ||''' IMMEDIATE;'
> from v\$session s, v\$process p
> where s.username = '${esquema}'
> and p.addr (+) = s.paddr;
> EOF

alter system kill session '598,60705' IMMEDIATE;
alter system kill session '13,6279' IMMEDIATE;
alter system kill session '595,49129' IMMEDIATE;
alter system kill session '591,46591' IMMEDIATE;
alter system kill session '16,47283' IMMEDIATE;
alter system kill session '403,34455' IMMEDIATE;
alter system kill session '604,37015' IMMEDIATE;

[oracle@nominabdf ~]$ more /tmp/killSession${esquema}.sql

alter system kill session '598,60705' IMMEDIATE;



alter system kill session '13,6279' IMMEDIATE;



alter system kill session '595,49129' IMMEDIATE;



alter system kill session '591,46591' IMMEDIATE;



alter system kill session '16,47283' IMMEDIATE;



alter system kill session '403,34455' IMMEDIATE;



alter system kill session '604,37015' IMMEDIATE;

Bicentenary