Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Number of records in cursor: from Oracle stored procedure to Java

784987Jul 14 2010 — edited Jul 14 2010
We call PL/SQL stored procedures from Java code to process Oracle 10g database. When we need to retrieve some data, we use SYS_REFCURSOR OUT parameter. The question is: how we can get number of records in the cursor without duplicated SELECT?
(The %ROWCOUNT cursor attribute does not return the total number of rows for a query prior to the first fetch. If we fetch in stored procedure, we lose data.)
We use the following:
CREATE OR REPLACE PROCEDURE GET_DATA(p_cursor OUT SYS_REFCURSOR,
nRecs OUT INTEGER)
IS
BEGIN
OPEN p_cursor FOR SELECT * FROM SOME_TABLE WHERE ...;
SELECT COUNT(0) INTO nRecs FROM SOME_TABLE WHERE ...;
...
END GET_DATA;

So can we avoid the second SELECT to return number of records? Or may be we can somehow get it from SYS_REFCURSOR in Java?
Thank you in advance..
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 11 2010
Added on Jul 14 2010
13 comments
3,270 views