| Is it possible to identify/Count records in ref cursor without actually fetching [message #575382] |
Tue, 22 January 2013 13:40  |
 |
anoopmanagoli
Messages: 14
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 #575408 is a reply to message #575382] |
Wed, 23 January 2013 00:50  |
 |
rishwinger
Messages: 101
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.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
