Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Q: NULL return REF CURSOR from a PL/SQL function

715809Mar 10 2010 — edited Mar 10 2010
I was told that PL/SQL does not handle properly NULL REF CURSORS.

Here's my implementation in a PL/SQL package

PACKAGE SPEC:

TYPE z_rec IS RECORD (
...);
TYPE z_cur IS REF CUR RETURN z_rec;

FUNCTION some_function(
...
p_msg OUT VARCHAR2)
RETURN z_cur;


PACKAGE BODY:

FUNCTION some_function(
...
p_msg OUT VARCHAR2)
RETURN z_cur

IS
retval z_cur;

OPEN retval FOR
SELECT ...

-- Successfull data retrieval
p_msg := NULL;
RETURN retval;

EXCEPTION
WHEN OTHERS THEN
p_msg := SUBSTR( SQLERRM, 1, 255 );
RETURN NULL;

END some_function;


I am expecting that a user of this function would call it and test p_msg (output parameter)

1. IS NULL p_msg it means there were no errors encounetered. The returned cursor can be NULL though (i.e. no records retrieved)

2. IS NOT NULL p_msg, it means that there were errors and the returned cursor is not usable.

My question is:
what are the pitfalls of this approach?

Thanks a lot.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 7 2010
Added on Mar 10 2010
5 comments
2,747 views