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

ado.net, metadata, oracle, package, stored-procedures

Solution

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.

Problem

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?

Original source