How to retrieve a list of parameters for an Oracle Stored Procedure

Brian Harvey

I am trying to create a windows form that will allow the end-user to configure which application variables should map to which oracle stored procedure parameter. Where I am stuck at the moment is how to go about retrieving a list of the parameters defined in the supplied stored procedure. The database is Oracle 10g. The development environment is VS 2005 and language is obviously C#.

Thanks in advance for any help you can give me!

Brian

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Brian Harvey18y ago
Accepted answer
VJ,

Thanks so much for your reply. After being frustrated about not finding anything directly related to what I needed to accomplish... I contacted our Oracle DBA group and they provided me with the following syntax. I have to say, what they gave me is EXACTLY what I needed!

select argument_name, in_out, data_type

from   all_arguments

where owner = '{schema name}' and

       package_name = '{name of package or null if not contained in an Oracle package}' and

       object_name = '{name of stored procedure}'

order by position
+1
Vijaya Kadiyala18y ago
Hi

Thank you very much this information. this is something new that i came to know :).

regards -- Vj
+1
Vijaya Kadiyala18y ago
Hi ,

there is no straight forward way to get this. use the below statement which gives you the declaration of stored procedure and manipulate the output.

select * from USER_SOURCE WHERE NAME ='PRC_TEMP' and line = 1

Thanks -- Vj

http://dotnetvj.blogspot.com

http://oravj.blogspot.com
+2