Java: Read metadata from procedure

I am looking for a way to get metadata from an Ora开发者_JAVA百科cle 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.

---

Use the below code block to fix the issue.

DatabaseMetaData dbMetaData = conn.getMetaData();
ResultSet rs = dbMetaData.getProcedureColumns(conn.getCatalog(),
                      null,
                      "procedureNamePattern",
                      "columnNamePattern");

while(rs.next()) {
  // get stored procedure metadata
  String procedureCatalog     = rs.getString(1);
  String procedureSchema      = rs.getString(2);
  String procedureName        = rs.getString(3);
  String columnName           = rs.getString(4);
  short  columnReturn         = rs.getShort(5);
  int    columnDataType       = rs.getInt(6);
  String columnReturnTypeName = rs.getString(7);
  int    columnPrecision      = rs.getInt(8);
  int    columnByteLength     = rs.getInt(9);
  short  columnScale          = rs.getShort(10);
  short  columnRadix          = rs.getShort(11);
  short  columnNullable       = rs.getShort(12);
  String columnRemarks        = rs.getString(13);

  System.out.println("stored Procedure name="+procedureName);
  System.out.println("procedureCatalog=" + procedureCatalog);
  System.out.println("procedureSchema=" + procedureSchema);
  System.out.println("procedureName=" + procedureName);
  System.out.println("columnName=" + columnName);
  System.out.println("columnReturn=" + columnReturn);
  System.out.println("columnDataType=" + columnDataType);
  System.out.println("columnReturnTypeName=" + columnReturnTypeName);
  System.out.println("columnPrecision=" + columnPrecision);
  System.out.println("columnByteLength=" + columnByteLength);
  System.out.println("columnScale=" + columnScale);
  System.out.println("columnRadix=" + columnRadix);
  System.out.println("columnNullable=" + columnNullable);
  System.out.println("columnRemarks=" + columnRemarks);
}

---

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 is the generic JDBC method to get the parameters for a procedure.

---

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.

---

If you want to avoid using JDBC directly, you can use my open source SchemaCrawler API, which creates plain old Java objects from database metadata.