Column headers continue to appear on Oracle output

Question

After 10 lines, my column headers reappear in my Oracle release. Is there anything about my code or any environment variable that I can stop to stop this? I need the column headings to appear once at the top of my results.

BREAK ON Customer COLUMN Customer - FORMAT A15 - HEADING 'Customer Name' COLUMN "Charter Date" - HEADING 'Charter|Date' COLUMN Pilot - FORMAT A20 - HEADING 'Pilot' SELECT DECODE (cu.cus_initial,null, cu.cus_fname||' '||cu.cus_lname, cu.cus_fname||' '||cu.cus_initial||'. '||cu.cus_lname) AS Customer, ch.char_date "Charter Date", TRIM( e.emp_fname) ||' '|| TRIM(e.emp_lname) AS "Pilot" FROM hartmar.customer cu, hartmar.charter ch, hartmar.crew cr, hartmar.pilot p, hartmar.employee e WHERE cu.cus_code = ch.cus_code AND ch.char_trip = cr.char_trip AND cr.emp_num = p.emp_num AND p.emp_num = e.emp_num AND cr.crew_type = 'Pilot' ORDER BY cu.cus_lname, cu.cus_fname, cu.cus_initial, ch.char_date ; CLEAR BREAKS CLEAR COLUMNS 

Answer 1

Assuming you run this in SQL * Plus, you need to set your format.

 SET PAGESIZE 50000 

will cause column headers to appear only once for every 50,000 rows. I think 50,000 is the maximum PAGESIZE setting.

If you want to completely exclude headers, you can set PAGESIZE to 0, but this will suppress even the first set of headers

 SQL> set pagesize 0; SQL> select ename, empno from emp; PAV 7623 smith 7369 ALLEN 7499 WARD 7521 JONES 7566 MARTIN 7654 BLAKE 7698 CLARK 7782 SCOTT 7788 KING 7839 TURNER 7844 ADAMS 7876 SM0 7900 FORD 7902 MILLER 7934 BAR 1234 16 rows selected. 

Answer 2

Use a hidden function that will suppress all EXCEPT in the first line of headers!

 set pagesize 0 embedded on 

Thanks to "Bruno Ruess" via https://community.oracle.com/thread/2389479?start=0&tstart=0 for the above.

If you then add

 SET UNDERLINE off 

Then you can suppress the “underline” of the title bar and move on to something that looks more like a CSV.

Answer 3

You also can:

 SET PAGESIZE 0 

To stop all column headings after the start of your report.