Number of records in cursor: from Oracle stored procedure to Java
784987Jul 14 2010 — edited Jul 14 2010We 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..