3

Normally for a user defined type I can simply do the following (Simplified but functional example):

//Create table
create type myType1 as object( a char(2), b char(2) );
create type myTable1 as table of myType1;

//Java code
ArrayDescriptor  des  = ArrayDescriptor.createDescriptor( "MYTABLE1", con);
StructDescriptor sDes = StructDescriptor.createDescriptor( des.getBaseName(), con);

//Populte the metadata
String  columnName = sDes.getColumnName(0);
int     oracleType = sDes.getColumnType(0);
int     maxSize    = sDes.getColumnDisplaySize(0);
boolean isNullable = sDes.isNullable(0)>0;

Except now I have a table that's defined with a primitive type instead of a struct, and I can't seem to access the metadata. My current code is:

//Create table
create or replace type myTable2 as table of char(2);

//Java code
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor( "MYTABLE2", con);
//This next line would throw an exception, as CHAR type is not a structure
//StructDescriptor sDes = StructDescriptor.createDescriptor( des.getBaseName(), con);

//Populte the metadata    
int     oracleType = descriptor.getBaseType(); // This Works
String  columnName = "COLUMN_VALUE";           // This Works (I think)
int     maxSize    = ????                      // How do I access the '2'?
boolean isNullable = ????                      // How do I access isNullable?
billoot
  • 77
  • 15

2 Answers2

1

I hope that I understand you right. If you can get column info, you can try following codes.

DatabaseMetaData databaseMetaData = connection.getMetaData();

ResultSet columns = databaseMetaData.getColumns(null,null, tableName, null);
while(columns.next())
{
    String columnName = columns.getString("COLUMN_NAME");
    String datatype = columns.getString("DATA_TYPE");
    String columnsize = columns.getString("COLUMN_SIZE");
    String decimaldigits = columns.getString("DECIMAL_DIGITS");
    String isNullable = columns.getString("IS_NULLABLE");
    String is_autoIncrment = columns.getString("IS_AUTOINCREMENT");
}
theMind
  • 194
  • 1
  • 10
  • I cannot get the column info for this table. The line `ResultSet columns = databaseMetaData.getColumns(null,null, tableName, null);` returns an empty `ResultSet`. – billoot Aug 13 '18 at 14:03
  • 1
    I updated the first example to better highlight the differences. – billoot Aug 13 '18 at 14:39
  • @billie i tried it and it works. Are you sure that your connection is truth. – theMind Aug 16 '18 at 07:04
  • 1
    I tried again using every combination I could think of to get it to work, no luck. The `ResultSet` is always empty. I am able to use this method to pull up information about other full tables, it just doesn't work for the array. – billoot Aug 16 '18 at 20:53
0

At times, in a real-world enterprise environment where you don't have admin perms on an Oracle DB, you might not be able to rely on Java's DatabaseMetaData class but you might be able to rely on DDL queries. By that, I mean that you might be able to execute DDL queries as you would normally run a SQL query in your Java app.

For example:

  1. You might be able to get the datatype of the column by executing the following query as shown in this answer: select t.data_type from user_tab_columns t where t.TABLE_NAME = 'MYTABLE1' and t.COLUMN_NAME='COLUMN_VALUE'.
  2. You might be able to get the display size of the column by executing the following query as shown in this answer: SELECT data_length FROM all_tab_columns WHERE table_name = 'MYTABLE1' AND column_name = 'COLUMN_VALUE'.
  3. You might be able to get the nullability of the column by executing the following query as shown in this answer: select nullable from user_tab_columns where table_name = 'MYTABLE1' and column_name = 'COLUMN_VALUE';.
entpnerd
  • 10,049
  • 8
  • 47
  • 68