Handle return empty cursor in SP output parameter [message #669334] |
Mon, 16 April 2018 05:42 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi all,
I have a SP that returns two cursors depending on an input parameter. In case the input parameter is (for example) > 1 then two cursors are returned, else 1 cursor is returned.
I need to handle the second case so that I dont return a cursor as null.
CREATE OR REPLACE PROCEDURE P_GET_cursor(
I_falg NUMBER,
cur1 OUT SYS_REFCURSOR,
cur2 OUT SYS_REFCURSOR
)
AS
BEGIN
IF I_falg = 0 THEN
OPEN cur1 FOR select 6 from dual;
ELSE
OPEN cur1 FOR select 7 from dual;
OPEN cur2 FOR select 8 from dual;;
END IF;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END;
The only way I know is to select from dual where 1=2, but this will load the SP with selects specially if the actual case has more than two cursors, is there another way?
Thanks,
Ferro
[Updated on: Mon, 16 April 2018 05:44] Report message to a moderator
|
|
|
Re: Handle return empty cursor in SP output parameter [message #669338 is a reply to message #669334] |
Mon, 16 April 2018 07:05 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The only options you have are:
1) open all the cursors in the procedure, using selects where 1=2 as necessary
2) catch and handle the invalid cursor error (-1001)
3) Change the code structure completely so that you the client is only trying to get refcursors that will actually return data in the first place.
|
|
|
|
|
|
|
Re: Handle return empty cursor in SP output parameter [message #669385 is a reply to message #669384] |
Thu, 19 April 2018 03:36 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Well you could add a column to indicate where the data came from if it helps, though if the structure is different it's probably more hassle than it's worth.
If you're doing different things with the different cursors then why are you using one procedure to return both? Doesn't the caller know which it needs?
|
|
|
|
|
|