Re: determine cursor parameter name of a stored procedure

From: Philipp Sumi <nospam_at_123123123.xxx>
Date: Sun, 21 Mar 2004 11:37:49 +0100
Message-ID: <c3jr9q$le7$1@newshispeed.ch>

Hello Mark

> you may be able to use the info in the all_arguments data dictionary view

Thank you very much, this was indeed what I was looking for :-D

I can determine the cursor parameter with the following query:

SELECT ARGUMENT_NAME FROM ALL_ARGUMENTS

     WHERE
       OBJECT_NAME = {0} AND
       DATA_TYPE = 'REF CURSOR'

...where the placeholder {0} is being replaced with the name of the inspected stored procedure.

Still, a few questions remain:

The query is case sensitive and all arguments / object names seem to be stored in capitals. Is that a configuration issue or may I generally convert the procedure name to upper case to get my results?
Is the DATA_TYPE always "REF CURSOR" or are there other common cursor types / techniques used to return data retrieve through a SELECT statement?
Does a user that may execute a given procedure always have permission to access the arguments of the procedure in ALL_ARGUMENTS?

Thanks again

Philipp Received on Sun Mar 21 2004 - 04:37:49 CST