Obtain stored procedure metadata for a procedure within an Oracle package using ADO.NET

I am trying to obtain the stored procedure metadata (procedure name,parameter types,parameter names etc) for a procedure declared within an Oracle package, using the standard ADO.NET API - DbConnection.GetSchema call. I am using the ODP driver.

I see that the Package is listed in the 'Packages' and 'PackageBodies' metadata collections. The procedure parameter appears in the 'Arguments' a开发者_运维问答nd 'ProcedureParameters' collections. I do not see a way to get to the procedure information via the package metadata. Even if the procedure does not have any parameters there is a row in the 'ProcedureParameters' collection for this procedure.

My question: To obtain the procedure metadata do I have to query the 'ProcedureParameters' collection and search for an entry with the required package name? I can then construct the procedure metadata based on the parameter information. Is there a shorter or quicker way to obtain the same information?

I'm not sure how you'd get this using ADO.NET, but you can directly query the database to get this information as follows:

SELECT *
  FROM SYS.DBA_PROCEDURES
  WHERE OBJECT_TYPE = 'PACKAGE' AND
        OBJECT_NAME = '<your package name here>' AND
        PROCEDURE_NAME IS NOT NULL;

Once you've run the above query you'll have a result set which has, among other things, the PROCEDURE_NAME. Given the package name and the PROCEDURE_NAME, you can find parameter info using the following query:

SELECT *
  FROM SYS.ALL_ARGUMENTS
  WHERE PACKAGE_NAME = '<your package name here>' AND
        OBJECT_NAME = '<PROCEDURE_NAME from query above>';

Share and enjoy.

With help from Bob I've used the following query to obtain a list of stored procedures defined within a package.

SELECT a.OBJECT_NAME,p.PROCEDURE_NAME FROM SYS.ALL_OBJECTS a, SYS.ALL_PROCEDURES p WHERE a.OBJECT_NAME = p.OBJECT_NAME AND a.OBJECT_TYPE = 'PACKAGE' AND a.OWNER = '" + ownerName + "' AND p.PROCEDURE_NAME IS NOT NULL"

This returns all stored procedures for a particular user. I can then use the 'ProcedureParameters' collection to obtain the parameter information for them.

NOTE: Do not query the SYS.DBA_PROCEDURES table. The user credentials you use to execute the query might not have 'select' privileges on that table.