CODEWITHSUNDEEP
javaoracle-databasestored-proceduresmetadata
Adnan
Adnan

Reputation: 26350

Java: Read metadata from procedure

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.

Upvotes: 1

Views: 8146

Answers (4)

Sualeh Fatehi
Sualeh Fatehi

Reputation: 4784

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.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 3

mithlesh
mithlesh

Reputation: 51

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);
}

Upvotes: 5

Codo
Codo

Reputation: 78835

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.

Upvotes: 2

Related Questions