Home » SQL & PL/SQL » SQL & PL/SQL » Displaying PAGE N of M through SQL*PLUS Report
Displaying PAGE N of M through SQL*PLUS Report [message #188878] Tue, 22 August 2006 04:39 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I have to display the page number in a report using SQL*PLUS in the format PAGE N of M.
If total number of pages are 10 then when displaying 2 page, it should give Page : 2 of 10.
For now I am able to get just the page number using :
BTITLE RIGHT 'Page ' FORMAT 999 SQL.PNO


Thanks,
Mona
Re: Displaying PAGE N of M through SQL*PLUS Report [message #188887 is a reply to message #188878] Tue, 22 August 2006 04:54 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
To know the total number of pages, the tool would need to select the entire query and buffer the result before emitting pages. SQL*Plus does not work like this - it emits pages as the rows are returned, rather than buffer them until the end.

I suggest you scrap this idea, or use a different tool. If you are very determined to flush your company's money down the lav, you could SELECT COUNT(*) with the same WHERE conditions, calculate the number of pages it will occupy, load that number into a replacement variable (eg. &PGCNT), then run the query. Of course, this would end up performing TWICE the work just to get a page count - an overhead most would agree is not warranted.

Alternatively, just use SQL*Plus to return the raw data and run it through another pager. There's one on Unix, but the name won't come to me right now - maybe "pr"??? Although I don't think even pr will give you the page count.

Ross Leishman
Previous Topic: How to write the trigger?
Next Topic: define statement in script
Goto Forum:
  


Current Time: Sun Dec 11 08:20:59 CST 2016

Total time taken to generate the page: 0.14718 seconds