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;

Could you give us all commands you've issued and in which order? — Marco, Feb 24 '15 at 07:01
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; — Marvin Wong, Feb 24 '15 at 07:03

Lalit Kumar B · Accepted Answer · 2020-05-01T00:08:53.580

48

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

edited May 01 '20 at 00:08

answered Feb 24 '15 at 07:01

Lalit Kumar B

47,486
13
97
124

Thanks for the reply. My version is 2.1.1.64. – Marvin Wong Feb 24 '15 at 07:40
1

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. – Lalit Kumar B Feb 24 '15 at 07:53
This doesn't answer the question. The poster asked how to do this using SQL Developer. – Terrible Tadpole Aug 15 '16 at 00:56
@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. – Lalit Kumar B Aug 15 '16 at 13:08

score 6 · Answer 2 · edited Jun 20 '18 at 14:38

6

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.

edited Jun 20 '18 at 14:38

Laurenz Albe

209,280
17
206
263

answered Jun 20 '18 at 14:24

Tomas Maracek

61
1
1

useful if you want headlines, but no underline. Otherwise, if you prefer just results, setting heading off will suffice. That is: 'SET heading OFF' – Ewoks Oct 14 '20 at 13:27

score 0 · Answer 3 · answered Apr 26 '23 at 08:09

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;

Remove Column Header into the Output Text file

3 Answers3

Linked

Related