CODEWITHSUNDEEP
javaoracle-databasejdbcdatabase-metadata
hoang
hoang

Reputation: 1594

Get metadata function in oracle

In oracle, i can get metadata of stored procedure:

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

But no data return with function:

DatabaseMetaData.getFunctionColumns()

How to get oracle function's metadata

Upvotes: 3

Views: 1564

Answers (2)

hoang
hoang

Reputation: 1594

It is not good but i work for me:

select ARGUMENT_NAME, DATA_TYPE, IN_OUT from SYS.ALL_ARGUMENTS where object_name = upper(?) order by position as

then in java, i can get 

String columnName = rs.getString("ARGUMENT_NAME");
if (index == 0) {
    columnName = RETURN_VALUE;
}
String columnDataTypeInString = rs.getString("DATA_TYPE");
String columnReturnInString = rs.getString("IN_OUT");

Upvotes: 1

hoang
hoang

Reputation: 1594

Something wrong, Elliott Frisch

It alway return the same result for different store/function:

Catalog Name = , Schema Name = , Table Name = , Column Name = PROCEDURE_CAT, Column Label = PROCEDURE_CAT, Column Data Type = 12, Column Class Name = java.lang.String, Column Return Type Name = VARCHAR2

Catalog Name = , Schema Name = , Table Name = , Column Name = PROCEDURE_SCHEM, Column Label = PROCEDURE_SCHEM, Column Data Type = 12, Column Class Name = java.lang.String, Column Return Type Name = VARCHAR2

Catalog Name = , Schema Name = , Table Name = , Column Name = PROCEDURE_NAME, Column Label = PROCEDURE_NAME, Column Data Type = 12, Column Class Name = java.lang.String, Column Return Type Name = VARCHAR2

Catalog Name = , Schema Name = , Table Name = , Column Name = COLUMN_NAME, Column Label = COLUMN_NAME, Column Data Type = 12, Column Class Name = java.lang.String, Column Return Type Name = VARCHAR2

Catalog Name = , Schema Name = , Table Name = , Column Name = COLUMN_TYPE, Column Label = COLUMN_TYPE, Column Data Type = 2, Column Class Name = java.math.BigDecimal, Column Return Type Name = NUMBER

Catalog Name = , Schema Name = , Table Name = , Column Name = DATA_TYPE, Column Label = DATA_TYPE, Column Data Type = 2, Column Class Name = java.math.BigDecimal, Column Return Type Name = NUMBER

Catalog Name = , Schema Name = , Table Name = , Column Name = TYPE_NAME, Column Label = TYPE_NAME, Column Data Type = 12, Column Class Name = java.lang.String, Column Return Type Name = VARCHAR2

Catalog Name = , Schema Name = , Table Name = , Column Name = PRECISION, Column Label = PRECISION, Column Data Type = 2, Column Class Name = java.math.BigDecimal, Column Return Type Name = NUMBER

Catalog Name = , Schema Name = , Table Name = , Column Name = LENGTH, Column Label = LENGTH, Column Data Type = 2, Column Class Name = java.math.BigDecimal, Column Return Type Name = NUMBER

Catalog Name = , Schema Name = , Table Name = , Column Name = SCALE, Column Label = SCALE, Column Data Type = 2, Column Class Name = java.math.BigDecimal, Column Return Type Name = NUMBER

Catalog Name = , Schema Name = , Table Name = , Column Name = RADIX, Column Label = RADIX, Column Data Type = 2, Column Class Name = java.math.BigDecimal, Column Return Type Name = NUMBER

Catalog Name = , Schema Name = , Table Name = , Column Name = NULLABLE, Column Label = NULLABLE, Column Data Type = 2, Column Class Name = java.math.BigDecimal, Column Return Type Name = NUMBER

Catalog Name = , Schema Name = , Table Name = , Column Name = REMARKS, Column Label = REMARKS, Column Data Type = 12, Column Class Name = java.lang.String, Column Return Type Name = VARCHAR2

Catalog Name = , Schema Name = , Table Name = , Column Name = SEQUENCE, Column Label = SEQUENCE, Column Data Type = 2, Column Class Name = java.math.BigDecimal, Column Return Type Name = NUMBER

Catalog Name = , Schema Name = , Table Name = , Column Name = OVERLOAD, Column Label = OVERLOAD, Column Data Type = 12, Column Class Name = java.lang.String, Column Return Type Name = VARCHAR2

Catalog Name = , Schema Name = , Table Name = , Column Name = DEFAULT_VALUE, Column Label = DEFAULT_VALUE, Column Data Type = -1, Column Class Name = java.lang.String, Column Return Type Name = LONG

Upvotes: 1

Related Questions