Using Result Set Meta Data Extensions

The oracle.jdbc.driver.OracleResultSetMetaData class is JDBC 2.0-compliant but does not implement the getSchemaName() and getTableName() methods because underlying protocol makes this unfeasible. Oracle does implement many methods to retrieve information about an Oracle result set, however.

The getColumnTypeName() method takes a column number and returns the SQL type name for columns of type REF, STRUCT, or ARRAY. In contrast, the getColumnType() method takes a column number and returns the SQL type. If the column stores an Oracle object or collection, then it returns an OracleTypes.STRUCT or an OracleTypes.ARRAY. For a list of the key methods provided by OracleResultSetMetadata, see "Class oracle.jdbc.driver.OracleResultSetMetaData".

The following example uses several of the methods in the OracleResultSetMetadata class to retrieve the number of columns from the EMP table, and each column's numerical type and SQL type name.

DatabaseMetaData dbmd = conn.getMetaData();
ResultSet rset = dbmd.getTables("", "SCOTT", "EMP", null);

 while (rset.next())
 {
   OracleResultSetMetaData orsmd = ((OracleResultSet)rset).getMetaData();
   int numColumns = orsmd.getColumnCount();
   System.out.println("Num of columns = " + numColumns);

   for (int i=0; i<numColumns; i++)
   {
     System.out.print ("Column Name=" + orsmd.getColumnName (i+1));
     System.out.print (" Type=" + orsmd.getColumnType (i + 1) );
     System.out.println (" Type Name=" + orsmd.getColumnTypeName (i + 1));
  }
}

The program returns the following output:

Num of columns = 5
Column Name=TABLE_CAT Type=12 Type Name=VARCHAR2
Column Name=TABLE_SCHEM Type=12 Type Name=VARCHAR2
Column Name=TABLE_NAME Type=12 Type Name=VARCHAR2
Column Name=TABLE_TYPE Type=12 Type Name=VARCHAR2
Column Name=TABLE_REMARKS Type=12 Type Name=VARCHAR2

---