Home » SQL & PL/SQL » SQL & PL/SQL » Is it possible to identify/Count records in ref cursor without actually fetching (9i)
Is it possible to identify/Count records in ref cursor without actually fetching [message #575382] Tue, 22 January 2013 13:40 Go to next message
anoopmanagoli
Messages: 20
Registered: August 2012
Location: india
Junior Member
/* Formatted on 22/01/2013 19:32:50 */
CREATE OR REPLACE PROCEDURE test_rdm_miles (
p_ref_cursor OUT SYS_REFCURSOR
p_success NUMBER)
IS
BEGIN
OPEN p_ref_cursor FOR
SELECT 5168 mem_uid,
16353 bac_uid,
'2013-JAN-19' dte,
3 no_of_pax,
'AnoopM' username,
NULL reward_id
FROM DUAL
UNION
SELECT 4702 mem_uid,
16344 bac_uid,
'2013-JAN-29' dte,
2 no_of_pax,
'RAZO' username,
NULL reward_id
FROM DUAL;

/* Need to return p_success as 0 when the ref cursor holds data and return p_success as 1 when an empty refcursor is returned*/


END;
/

Hi all,

I have a Procedure with out parameters as a REF CURSOR and response message as p_success.

This ref cursor will be returned to the calling service.
Is there a way in oracle by which we can identify whether the Ref cursor holds data without actually fetching it.
Since if i choose to fetch the data, i will lose one row when i return the ref cursor back to the calling service Or else is there way i can retrieve the row i lose during fetch.

Other alternative what have been suggested is create an object type ,fetch the ref cursor values in object type. Then i can use the ref cursor to return the data by table casting.

one more solution is

OPEN
FETCH
CLOSE

OPEN (AGAIN) { this will lead to redundancy)

In reality the select statement will have is huge lines of code therefore want a suggestion whether there is an alternative to the above solution.

Please suggest.
Re: Is it possible to identify/Count records in ref cursor without actually fetching [message #575383 is a reply to message #575382] Tue, 22 January 2013 13:53 Go to previous messageGo to next message
anoopmanagoli
Messages: 20
Registered: August 2012
Location: india
Junior Member
as such we know the ref cursor by itself do not hold data. so any solutions how such a situation is dealt.
Re: Is it possible to identify/Count records in ref cursor without actually fetching [message #575384 is a reply to message #575382] Tue, 22 January 2013 14:13 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you need to know if there is something "in" the cursor or not?
Just fetch it, if there is something then handle it otherwise leave.

Regards
Michel
Re: Is it possible to identify/Count records in ref cursor without actually fetching [message #575408 is a reply to message #575382] Wed, 23 January 2013 00:50 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member

SQL> CREATE OR REPLACE PROCEDURE test_rdm_miles (
  2  p_ref_cursor OUT SYS_REFCURSOR,
  3  p_success NUMBER)
  4  IS
  5  BEGIN
  6  OPEN p_ref_cursor FOR
  7  SELECT 5168 mem_uid,
  8  16353 bal_countuid,
  9  '2013-JAN-19' dte,
 10  3 no_of_pax,
 11  'AnoopM' username,
 12  NULL reward_id
 13  FROM DUAL
 14  UNION
 15  SELECT 4702 mem_uid,
 16  16344 bal_countuid,
 17  '2013-JAN-29' dte,
 18  2 no_of_pax,
 19  'RAZO' username,
 20  NULL reward_id
 21  FROM DUAL;
 22
 23  /* Need to return p_success as 0 when the ref cursor holds data and retur
p_success as 1 when an empty refcursor is returned*/
 24
 25
 26  END;
 27  /

Procedure created.

SQL> var ris refcursor
SQL> exec test_rdm_miles(:ris,1);

PL/SQL procedure successfully completed.

SQL> print ris

   MEM_UID BAL_COUNTUID DTE          NO_OF_PAX USERNA R
---------- ------------ ----------- ---------- ------ -
      4702        16344 2013-JAN-29          2 RAZO
      5168        16353 2013-JAN-19          3 AnoopM

SQL> DECLARE
  2    ris SYS_REFCURSOR;
  3    l_count INTEGER;
  4  BEGIN
  5    test_rdm_miles(ris,1);
  6    SELECT COUNT(*) INTO l_count FROM
  7      (SELECT * FROM TABLE (XMLSEQUENCE(ris))
  8      ) t1;
  9    dbms_output.put_line('Row_Count='||l_count);
 10  END;
 11  /
Row_Count=2

PL/SQL procedure successfully completed.


Re: Is it possible to identify/Count records in ref cursor without actually fetching [message #575413 is a reply to message #575408] Wed, 23 January 2013 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But when you have counted you cannot retrieve the rows you have to call again the procedure which may return another number of rows.
In addition, OP does not want the count but if there is a row or not.
Imagine the cursor returns one billion rows, you have to wait minutes if not an hour to get a count you don't care.

Regards
Michel
Re: Is it possible to identify/Count records in ref cursor without actually fetching [message #575414 is a reply to message #575408] Wed, 23 January 2013 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And, in the end, are you sure this works in 9i?

Regards
Michel
Re: Is it possible to identify/Count records in ref cursor without actually fetching [message #575415 is a reply to message #575414] Wed, 23 January 2013 01:25 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Sad

Sorry My bad, I forget to mention i tested this in 10g
Re: Is it possible to identify/Count records in ref cursor without actually fetching [message #575419 is a reply to message #575415] Wed, 23 January 2013 01:35 Go to previous message
Michel Cadot
Messages: 60018
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, next time verify OP's version before providing something.

Regards
Michel
Previous Topic: moving data to another table
Next Topic: PL/SQL Anonymous block
Goto Forum:
  


Current Time: Mon Dec 22 21:19:06 CST 2014

Total time taken to generate the page: 0.09693 seconds