Path: utzoo!attcan!uunet!wuarchive!cs.utexas.edu!usc!snorkelwacker!spdcc!rayssd!pal
From: pal@sgfb.ssd.ray.com (Paul A. Levy)
Newsgroups: comp.databases
Subject: Re: Oracle SQL*Plus: How do I put page number on query?
Message-ID: <157@sgfb.ssd.ray.com>
Date: 19 Jul 90 15:38:38 GMT
References: <1990Jul17.173930.12616@ariel.unm.edu>
Sender: news@rayssd.ssd.ray.com
Organization: Raytheon Company, Portsmouth RI
Lines: 65



To get the Page Number into an ORACLE SQL*Plus report.

SQL.PNO         Oracle variable that holds the current page number.

SQL*Plus User's Guide and Reference for version 3.0 has an
explanation under the TTITLE command.

If the first entry after the TTITLE is a new-form command then Oracle 
will use the new-form of TTI, else the old form is assumed.  The 
new-form commands are COL, SKIP, TAB, LEFT, CENTER, RIGHT, BOLD and
FORMAT.

The simplest example is:

TTI R sql.pno 

Just to ramble on.  I use something like the following to put a TTITLE 
or BTITLE on a page.  This puts the 'date' and a 'Pg # of #' in the title.

COL datevar NEW_V today    NOPRI
COL totpg   NEW_V totalpgs NOPRI

REM   get today's date

SELECT sysdate datevar from dual;

REM   this command figures out the total pages to be printed
REM   by executing the main query and dividing by the number of
REM   records (not lines) printed on a page.  This ONLY works
REM   if each record occupies the same number of printed lines.
REM   The SET PAGES= requires careful calculation to assure 
REM   that a page break doesn't occur in the middle of multiple line
REM   records.  CEIL function returns the lowest integer that equals
REM   or exceeds a value.

SELECT ceil(count(*)/(records_per_page)) totpg
FROM   tablename
WHERE  same_conditions_as_main_query;

TTI LE today CE 'Title of Report' R 'Pg' FORMAT 999 sql.pno  -
    ' of' FORMAT 999 totalpgs;

REM  main query

SELECT stuff
FROM   tablename
WHERE  conditions;


Paul Levy
------------------------------------------------------------------------------
Internet: pal@sgfb.ssd.ray.com 
UUCP: {decuac,gatech,mimsy,mirror,necntc,sun,uiucdcs,ukma}!rayssdb!pal
------------------------------------------------------------------------------
The opinions expressed herein are mine.
-- 

Paul Levy
------------------------------------------------------------------------------
Internet: pal@sgfb.ssd.ray.com 
UUCP: {decuac,gatech,mimsy,mirror,necntc,sun,uiucdcs,ukma}!rayssdb!pal
------------------------------------------------------------------------------
The opinions expressed herein are mine.