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
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

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 :

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
Messages: 3727
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: Fri Jul 21 00:10:41 CDT 2017

Total time taken to generate the page: 0.10571 seconds