Java: Read metadata from procedure

Question

I am looking for a way to get metadata from an Oracle store procedure, such as input/output parameters and their types.

I did try DESC but it is not working:

stmt = conn.createStatement();
ResultSet rs1 = stmt.executeQuery("desc pack.procname");
while ( rs1.next() ) { 
System.out.println(rs1.getString(1));
}

Any ideas on what approach to use to get the input/output parameters?

Thanx for your time.

Answer 1

Try the following statement:

select *
from user_arguments
where package_name = 'PACK' and object_name = 'PROCNAME';

Depending on the schema the package belongs to, you might need to use the view _ALL_ARGUMENTS_ or _DBA_ARGUMENTS_ instead.

Answer 2

Since you are using JDBC, my preference would be to use the JDBC metadata API to retrieve this information rather than querying the Oracle data dictionary directly. [DatabaseMetaData.getProcedureColumns][1] is the generic JDBC method to get the parameters for a procedure.

[1]: http://download.oracle.com/javase/1.4.2/docs/api/java/sql/DatabaseMetaData.html#getProcedureColumns(java.lang.String, java.lang.String, java.lang.String, java.lang.String)