I want to identify all my stored functions which return REFCURSOR.
Is there a way to do that?
I can't find this information anywhere.
I searched in dba_functions,dba_objects,dba_arguments...
What is the (best) way to achieve it?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Based on a quick and simple test:
select * from all_arguments where position = 0 and data_level = 0 and argument_name is null and data_type = 'REF CURSOR';
position 0 is the first argument of a program unit. If it does not have a name/level (
data_level = 0 and argument_name is null), then it is the return argument of the function, and you are looking for
sys_refcursor type, which is displayed as
REF CURSOR in this view.