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

Question

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

Answer 1

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.

Answer 2

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.