Oracle SQLPLUS - How do I output header only once when spooling more than 50,000 records?

Brian_700 0
Viewed 4765

I am spooling a large result to disk and want to include the headers only once. I want headings but only once, not every 50,000 records. Based on the documentation I seem to be limited to 50,000 records before the header is printed a second time. According to the documentation 50,000 is the highest value.

I've tried setting the pagesize to zero and headings on - but that still produces a file with no header:

set pagesize 0
set heading on

Ideally I want to be able to perform a line count on the output file in unix:

wc -l result-file.txt

and it equals the Number of records in the query + 1.

sqlplus oracle
4 Answers

You can simply UNION the headers to the query itself, as in:

SELECT 'FIRST COLUMN' AS COLUMN1,
       'NEXT COLUMN' AS COLUMN2,
       'OTHER COLUMN' AS COLUMN3
  FROM DUAL
UNION ALL
SELECT a.COLUMN1,
       a.COLUMN2,
       b.COLUMN3
  FROM TABLE1 a
  INNER JOIN TABLE2 b
    ON b.KEY_FIELD = a.KEY_FIELD

Done this way you'll get the headers first followed by all the 'real' data.

user_2131360

How about something like this:

  • first select headings,
  • then rest of data - before that select, set pagesize 0

Drawback is an empty line (that contains only separators). I don't know how to get rid of it.

Here you go:

p.sql

-- general settings
set termout off 
set feedback off
set colsep ';'

-- headings
col empno format a10
col ename format a10
col salary format a10
spool emps.txt
select null as empno, null as ename, null as salary from dual;

-- data
set pagesize 0
col empno format 99999
col ename format a10
col salary format 999G990
select empno, ename, sal from emp;
spool off

Calling the script: 

c:\Temp>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.2.0 Production on ╚et Lis 24 19:31:38 2019

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

SQL> @p
SQL>

Result:

EMPNO     ;ENAME     ;SALARY                                                    
----------;----------;----------                                                
          ;          ;                          --> this is line I'm talking about                                   
  7369;SMITH     ;       800                                                    
  7499;ALLEN     ;      1600                                                    
  7521;WARD      ;      1250                                                    
  7566;JONES     ;      2975                                                    
  7654;MARTIN    ;      1250                                                    
  7698;BLAKE     ;      2850                                                    
  7782;CLARK     ;      2450                                                    
  7788;SCOTT     ;      3000                                                    
  7839;KING      ;      5000                                                    
  7844;TURNER    ;      1500                                                    
  7876;ADAMS     ;      1100                                                    
  7900;JAMES     ;       950                                                    
  7902;FORD      ;      3000                                                    
  7934;MILLER    ;      1300
user_90979060
user_122502070

Please run following command then run your SQL: set pagesize 999

user_78833880
Related