This discussion is archived
3 Replies Latest reply: Aug 13, 2013 7:04 AM by BrunoRuess RSS

Column headers in SQLPLUS query

660515 Newbie
Currently Being Moderated
Hi I am running a simple SQL query in SQLPLUS, and I want the column headers to appear just once at the top.

If I set the 'SET PAGESIZE' to 50000 (which I thought was the max) it repeats the column headers evert 50000 lines.

If I set 'SET PAGESIZE 50001', the column header appears every 14 lines


Anyone help ?

Thanks

Mike




Here is my SQLPLUS statement

SET HEADING ON
SET PAGESIZE 50001
SET LINESIZE 80
SET TERMOUT OFF
SET VERIFY OFF
SET FEEDBACK OFF
SET TRIMS OFF
SET COLSEP "|"
SET CONCAT "."
SET CONCAT "!"
set newpage none
set underline off



SPOOL &1


column Product format a4
column Division format a3
column Company format a4
column Geography format a4
column Customer format a12
column Currency format a3
column Year format a4
column Account format a7
column Period format a3



SELECT /*+ index(GB,GL_BALANCES_N2) */

'P'||GCC.SEGMENT8 Product,

'D'||GCC.SEGMENT4 Division,

'C'||GCC.SEGMENT1 Company,

'G'||GCC.SEGMENT5 Geography,

'G'||GCC.SEGMENT7 Customer,

'&2' Currency,

'FY'||substr(GB.PERIOD_NAME,5,2) Year,

'A'||GCC.SEGMENT2 Account,

substr(GB.PERIOD_NAME, 1, 3) Period,

trim(to_char(SUM((NVL(GB.PERIOD_NET_DR,0)-NVL(GB.PERIOD_NET_CR,0))*DECODE(SUBSTR(GCC.SEGMENT2,1,1),'4',(-1),1)),'999999999990.99')) VALUE

FROM

GL.GL_BALANCES GB,

GL.GL_CODE_COMBINATIONS GCC

WHERE GCC.CHART_OF_ACCOUNTS_ID=101

AND GB.CODE_COMBINATION_ID=GCC.CODE_COMBINATION_ID

AND GB.SET_OF_BOOKS_ID = &3

AND GB.ACTUAL_FLAG = 'A'

AND GB.CURRENCY_CODE = '&2'

AND GCC.SEGMENT2 BETWEEN '400000' AND '999999'

AND NOT (GCC.SEGMENT2 BETWEEN '899501' AND '899506')

AND GCC.SEGMENT2 != '899999'

AND GB.PERIOD_NAME in &4


GROUP BY

'P'||GCC.SEGMENT8,

'D'||GCC.SEGMENT4,

'C'||GCC.SEGMENT1,

'G'||GCC.SEGMENT5,

'G'||GCC.SEGMENT7,

'&2',

'FY'||substr(GB.PERIOD_NAME,5,2),

'A'||GCC.SEGMENT2,

substr(GB.PERIOD_NAME, 1, 3)

HAVING SUM(NVL(GB.PERIOD_NET_DR,0)) - SUM(NVL(GB.PERIOD_NET_CR,0)) <> 0

ORDER BY

'P'||GCC.SEGMENT8,

'D'||GCC.SEGMENT4,

'C'||GCC.SEGMENT1,

'G'||GCC.SEGMENT5,

'G'||GCC.SEGMENT7,

'&2',

'FY'||substr(GB.PERIOD_NAME,5,2),

'A'||GCC.SEGMENT2,

substr(GB.PERIOD_NAME, 1, 3);



spool off

exit
  • 1. Re: Column headers in SQLPLUS query
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Ranzalman wrote:
    Hi I am running a simple SQL query in SQLPLUS, and I want the column headers to appear just once at the top.

    If I set the 'SET PAGESIZE' to 50000 (which I thought was the max) it repeats the column headers evert 50000 lines.

    If I set 'SET PAGESIZE 50001', the column header appears every 14 lines
    Apparantly, 50,000 lines is the max. If you try to set PEGESIZE higher than that, you get the default, which is 14.

    The best thing might be to SET PAGESIZE 0, which turns off paging. Unfortunately, that turns off the automatic headers, too, so you'll have to supply your own header, manually, like this:
    SPOOL  &1
    
    ...
    
    SET   PAGESIZE    0
    
    PROMPT  PROD|DIV|COMP|GEOG|CUSTOMER    |CUR|YEAR|ACCOUNT|PER|VALUE
     
    SELECT /*+ index(GB,GL_BALANCES_N2) */ 
    ...
  • 2. Re: Column headers in SQLPLUS query
    660515 Newbie
    Currently Being Moderated
    Excellent Frank - that has the desired effect - thanks
  • 3. Re: Column headers in SQLPLUS query
    BrunoRuess Newbie
    Currently Being Moderated

    Use a 'hidden' feature:

    set pagesize 0 embedded on

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points