Q: NULL return REF CURSOR from a PL/SQL function
715809Mar 10 2010 — edited Mar 10 2010I 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.