How to get names of Objects inside Custom Oracle Types in JDBC

Question

I have a custom Oracle Type as shown below

CREATE TYPE bank_account AS OBJECT ( 
ACC_NUMBER(5),
BALANCE     NUMBER,
STATUS      VARCHAR2(10));

I'm using STRUCT class in JDBC to get the values inside type. I'm able to get the values stored in the type (bank_account) as an ARRAY [54453, 23234, 'ACTIVE'].
But I'm not able to get the object names corresponding to it like "ACC_NUMBER" , "BALANCE" and "STATUS" by any means.

I have seen JPublisher which creates classes corresponding to sql types. But is there any other straight forward way where I can get the META DATA of the object names inside type objects directly in jdbc

Answer 1

Yes, you can use StructDescriptor and ResultSetMetaData classes. I assume that you're calling a stored procedure in Oracle (in my code the first param is an int (IN param) and the second param is a bank_account, which is an OUT param)

The JDBC code

  String sqlQuery = "{ call some_procedure(?, ?)}";
  final StructDescriptor structDescriptor = StructDescriptor.createDescriptor("bank_account", conn);        
  final ResultSetMetaData metaData = structDescriptor.getMetaData();
  CallableStatement stmt= conn.prepareCall(sqlQuery);
  stmt.setInt(1, 150);
  stmt.registerOutParameter(2, java.sql.Types.STRUCT, "bank_account");

  stmt.execute();

  Object o = stmt.getObject(2); // this will return an OUT param either as a STRUCT object or a bank_account object. A bank_account will be returned if your Java bank_account class has implemented the SQLData interface.

  for(int i = 1; i <= metaData.getColumnCount(); ++i)
          System.out.println(metaData.getColumnName(i)); //this will print attribute names

The code should print

  ACC_NUMBER
  BALANCE
  STATUS