Re: determine cursor parameter name of a stored procedure

"Philipp Sumi" <nospam_at_123123123.xxx> wrote in message news:c3jr9q$le7$1_at_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?
|

all oracle non-java object names (tables, triggers, column names, parameter names) are converted to upper case when stored in the data dictionary references to the objects is case-insensitive in SQL and PL/SQL statements, unless the object name is being used to retrieved or convey metadata -- ie, when selecting from the data dictionary or passing an object name to a function or procedure that expects an object name

however, if an object name is enclosed in "double quotes" when created, oracle store the name litterally without upper case conversion and without checking validity (other than duplicity) -- this practice should be avoided, as the object will always need to be referenced in double quotes:

create table " " ( -- space as table name

     " " number -- tab as column name   , "case Preserved!" number -- mixed case, space and illegal character preserved
)

describe " "

 Name                    Null?    Type
 ----------------------- -------- --------
                                  NUMBER
 case Preserved!                  NUMBER

| - Is the DATA_TYPE always "REF CURSOR" or are there other common cursor
| types / techniques used to return data retrieve through a SELECT
statement?

some developers may return data in a collection (PL/SQL index-by table, Nested Table type, VARRAY type) or as XML in an XMLTYPE or even in a clob or blob or varchar2 (hopefully not the latter 3) -- but all return parameters will be type OUT or IN/OUT (see the IN_OUT column in ALL_ARGUMENTS) piplelined functions can also return a 'data stream'

|
| - Does a user that may execute a given procedure always have permission
| to access the arguments of the procedure in ALL_ARGUMENTS?
|
|

should have, unless you have a non-standard installation (ie, DBA revokes standard privileges or alters data dictionary views) the ALL_* version of the views are designed to expose to the user all available objects on which the user has privileges granted, either directly or indirectly

| Thanks again
|
| Philipp
|
|

;-{ mcs Received on Sun Mar 21 2004 - 06:14:52 CST