Home » SQL & PL/SQL » SQL & PL/SQL » Handle return empty cursor in SP output parameter (11.0.2.10)
Handle return empty cursor in SP output parameter [message #669334] Mon, 16 April 2018 05:42 Go to next message
OraFerro
Messages: 340
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 Go to previous messageGo to next message
cookiemonster
Messages: 13391
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 #669354 is a reply to message #669338] Mon, 16 April 2018 14:29 Go to previous messageGo to next message
Bill B
Messages: 1821
Registered: December 2004
Senior Member
Is the structure of the data being returned the same from the 2 queries or is it different?
Re: Handle return empty cursor in SP output parameter [message #669370 is a reply to message #669354] Tue, 17 April 2018 23:56 Go to previous messageGo to next message
OraFerro
Messages: 340
Registered: July 2011
Senior Member
Dear Bill B,

Both structures are different. But would like to learn you suggested solution in case there were.

Thanks,
Ferro
Re: Handle return empty cursor in SP output parameter [message #669373 is a reply to message #669370] Wed, 18 April 2018 03:34 Go to previous messageGo to next message
cookiemonster
Messages: 13391
Registered: September 2008
Location: Rainy Manchester
Senior Member
If they're the same structure combine them into one query with UNION (ALL)
Re: Handle return empty cursor in SP output parameter [message #669384 is a reply to message #669373] Thu, 19 April 2018 00:08 Go to previous messageGo to next message
OraFerro
Messages: 340
Registered: July 2011
Senior Member
Dear Cookiemonster,

Thanks for your reply but combining both cursors into one will dictate splitting them again at the caller procedure level where each cursor's data should be handled differently. For example, each would be inserted in a different table.

Thanks,
Ferro
Re: Handle return empty cursor in SP output parameter [message #669385 is a reply to message #669384] Thu, 19 April 2018 03:36 Go to previous messageGo to next message
cookiemonster
Messages: 13391
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?
Re: Handle return empty cursor in SP output parameter [message #669403 is a reply to message #669385] Sat, 21 April 2018 23:30 Go to previous messageGo to next message
OraFerro
Messages: 340
Registered: July 2011
Senior Member
Thanks for your care cookiemonster,

In some cases the same business dictates that the called SP sends one or two cursors based on a logical condition.
For example:
1- List of data and list of warnings. In this case the caller either receives both cursors not empty or only the first one not empty.
2- sending a list of employees, or a list of employee and another list for employees dependents (family members). In this case the caller either receives both lists not empty or just the first one not empty.

I welcome any ideas or suggestions that might improve the way I handle such cases by writing a SP that might return an empty cursor.

Thanks again,
Ferro
Re: Handle return empty cursor in SP output parameter [message #669441 is a reply to message #669403] Mon, 23 April 2018 02:57 Go to previous messageGo to next message
cookiemonster
Messages: 13391
Registered: September 2008
Location: Rainy Manchester
Senior Member
Nothing you've said there actually explains why you need one SP rather than two.
My list of options above covers all the possibilities. I would instinctively prefer option 3 but I don't know enough about your app to say how useful it is.
Re: Handle return empty cursor in SP output parameter [message #669445 is a reply to message #669441] Mon, 23 April 2018 04:54 Go to previous message
OraFerro
Messages: 340
Registered: July 2011
Senior Member
Thanks a lot.
Previous Topic: Polish Characters are missing through the SQLPLUS
Next Topic: Pagination
Goto Forum:
  


Current Time: Fri Dec 14 12:10:08 CST 2018