PLSQL Execute another Select statement if First is Empty with ref cursor

Question

I am trying to do something similar to the following:

procedure some_name ( p_ref_cursor in out nocopy sys_refcursor )

is

begin

open p_ref_cursor for
select item1, item2 from (some complicated join query);

fetch p_ref_cursor into xx;
if p_ref_cursor%NOTFOUND
then
select item1, item2 from table2 where item3='y';
end if;

end

If the first select statement returns nothing it should execute the second select statement to retrieve values. The problem I am running into is when the cursor is fetched, the result set is cleared out. So regardless if the first select statement found anything, it acts as if it was not found.

I have tried the following:

procedure some_name ( p_ref_cursor in out nocopy sys_refcursor )

is
local_var1 number
local_var2 number
begin

open p_ref_cursor for
select item1, item2 
into local_var1, local_var2
from (some complicated join query);

if local_var1 is null
then
select item1, item2 from table2 where item3='y';
end if;

end

However it looks like the INTO statement does not work with ref cursor.

Thanks, Dan

What I ended up doing was to return two ref cursors. Thanks. — Dan
– Dan, Commented Aug 11, 2014 at 13:01

xQbert · Accepted Answer · 2014-08-08 14:09:33Z

1

This seems like overkill for something that I think should work. But I believe it would work around the problem and eliminate the need for your if statements. Additionally it may be slightly slower since it has to do both queries. So if performance is of the utmost concern, then I wouldn't use this.

WITH CTE AS
(SELECT item1, item2, '1' as Src 
FROM (some complicated join query)
UNION
SELECT item1, item2, '2' as SRC
FROM table2 where item3='y')
SELECT item1, item2
FROM CTE
WHERE src = (Select min(src) from cte)

answered Aug 8, 2014 at 14:09

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dan Over a year ago

Thank you for your answer. Performance is a concern so I do not want to do a union :(

Bob Jarvis - Слава Україні Over a year ago

Try using UNION ALL instead of UNION. UNION eliminates duplicates in the result set, which can be very time consuming; UNION ALL dispenses with the duplicate checking, and often provides much better performance. Share and enjoy.

Collectives™ on Stack Overflow

PLSQL Execute another Select statement if First is Empty with ref cursor

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related