470,848 Members | 959 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,848 developers. It's quick & easy.

PL/SQL - ref cursor

I have a stored procedure which returns several ref cursors. I have a
few requirements which I am trying to achieve.

1) Ensure SQL is easy to validate by inspection
2) Execute minimum number of SQL
3) Ensure all refCursors are valid
Sample Code

PROCEDURE GetStuff (key varchar2, ctable1 OUT refCursor, ctable2 OUT
refCursor,
ctable3 OUT refCursor)
IS
BEGIN
OPEN ctable1 FOR
select * from table1 where primarykey = key;

OPEN ctable2 FOR
select * from table2 where key = (
select pkey from table1 where primarykey = key);

OPEN ctable2 FOR
select * from table3 where key in (
select field from table2 where key =
(select pkey from table3 where primarykey = key));

EXCEPTION
when no_data_found then
null;
END GetStuff;
Problems

1) SQL is easy to validate by using nested queries (often exists) but
is it efficient?

2) I dont know if there is something else I can set the refcursor to.
It seems poor practise to do something like
IF (missing(pkey)) THEN
select * from table3 where rownum<1;
END IF;I would need to do an extra select to get the pkey of course
which may be wasteful

3) I tried wrapping IF statements around child tables but unless the
refcursor is valid (i.e. executed I guess) the client code errors - 0
rows are fine but not executing the select causes issues.

Any ideas?
Jul 19 '05 #1
0 9271

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Vlad Simionescu | last post: by
2 posts views Thread by dave | last post: by
5 posts views Thread by Lespaul36 | last post: by
2 posts views Thread by Jim Frazer | last post: by
7 posts views Thread by Academic | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.