3 Replies Latest reply: Aug 13, 2013 9:04 AM by Bruno Ruess RSS

    Column headers in SQLPLUS query

    660515
      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
          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
            Excellent Frank - that has the desired effect - thanks
            • 3. Re: Column headers in SQLPLUS query
              Bruno Ruess

              Use a 'hidden' feature:

              set pagesize 0 embedded on