Avoiding a Double Cursor...

From: <redrobot5050_at_gmail.com>
Date: 29 Sep 2005 07:54:28 -0700
Message-ID: <1128005668.916793.65070@g44g2000cwa.googlegroups.com>

Another question about web apps built with the PL/SQL web cartridge. We have procedures which display data from the db to the user in a table format. We show the first 45 results, and if the user needs more, there are links to other "page numbers" at the bottom of the table.

To properly limit our data, we need a count of all the records that are going to be returned by the query.

Since we use weak reference cursors, the rowcount attribute (in Oracle 9i) only tells us which row we're currently on. So we loop through the whole cursor one time, counting up all the rows. Then we close and re-open the cursor, and start iterating through it, displaying rows to the user.

Is there a better way to get the total results returned? I have tried wrapping the query in a "SELECT COUNT (*) FROM ( << query goes here >> )" and then executing immediate, but that does not seem to work either.

does anyone have suggestions?

Thanks Received on Thu Sep 29 2005 - 09:54:28 CDT