Re: determine cursor parameter name of a stored procedure

On Sun, 21 Mar 2004 11:37:49 +0100, Philipp Sumi <nospam_at_123123123.xxx> wrote:

>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?

If you want mixed case identifiers in Oracle, you need to enclose them in double quotes. So by default everything is in upper case.
>
>- 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?
>

Please try to get familiar with the structure of the datadictionary. The purpose of the ALL_ views is to show *all* objects the user has access to.

>
>Thanks again
>
>Philipp
>

--
Sybrand Bakker, Senior Oracle DBA