Java Database Connectivity (JDBC)

I've written a generic utility for migrating tables from one backend database to another. It utilizes the JDBC DatabaseMetaData.getTypeInfo() method to return both the universe of java.sql.Types that the backend supports, and the mapping from java.sql.Types to the specific database data type. In theory, if I run this against both the source and the destination database JDBC driver, I should be able to write create table statements under program control from any backend to any backend - presuming that they both support java.sql.Types for all the columns. And, in fact, this is what I've done. In the event that the source database supports a java.sql.Types type that the destination database does not - I error out with a message describing the difficulty.

For its maiden voyage, I had it read a SQL Server database as a source, and an Oracle database as a destination. Imagine my surprise when my program reported that it couldn't migrate the table because Oracle doesn't support java.sql.Types = 3 (DECIMAL)! At first I thought this must be my bug. But, I've run a tester program against two different Oracle type 4 drivers using DatabaseMetaData.getTypeInfo() and neither of them return ResultSet output that lists a DATA_TYPE of 3 as one of the supported java.sql.Types.

What goes on here? I would think that DECIMAL would map to Oracle NUMBER type quite well.

Here's the business portion of the simplified tester program:

//*****************
void tester(Connection c)
{
try
{
DatabaseMetaData dbmd = c.getMetaData();

ResultSet r = dbmd.getTypeInfo();

while(r.next())
{
System.out.println(r.getString("TYPE_NAME") + " | " +
r.getInt("DATA_TYPE"));
}
}
catch(SQLException se) {}
}
}

...and here's it's output:

INTERVALDS | -104
INTERVALYM | -103
TIMESTAMP WITH LOCAL TIME ZONE | -102
TIMESTAMP WITH TIME ZONE | -101
NUMBER | -7
NUMBER | -6
NUMBER | -5
LONG RAW | -4
RAW | -3
LONG | -1
CHAR | 1
NUMBER | 2
NUMBER | 4
NUMBER | 5
FLOAT | 6
REAL | 7
VARCHAR2 | 12
DATE | 91
DATE | 92
TIMESTAMP | 93
STRUCT | 2002
ARRAY | 2003
BLOB | 2004
CLOB | 2005
REF | 2006

If I map to java.sql.types I get this:

INTERVALDS | null
INTERVALYM | null
TIMESTAMP WITH LOCAL TIME ZONE | null
TIMESTAMP WITH TIME ZONE | null
NUMBER | BIT
NUMBER | TINYINT
NUMBER | BIGINT
LONG RAW | LONGVARBINARY
RAW | VARBINARY
LONG | LONGVARCHAR
CHAR | CHAR
NUMBER | NUMERIC
NUMBER | INTEGER
NUMBER | SMALLINT
FLOAT | FLOAT
REAL | REAL
VARCHAR2 | VARCHAR
DATE | DATE
DATE | TIME
TIMESTAMP | TIMESTAMP
STRUCT | STRUCT
ARRAY | ARRAY
BLOB | BLOB
CLOB | CLOB
REF | REF

Notice that NUMERIC is listed - but not DECIMAL.

SO - what is my question? First off, have I missed something obvious? If not, second question - is there a work around short of checking to see whether getDatabaseProductName() returns 'Oracle' and building my mapping tables from hard code instead of the Oracle driver?

Thanks in advance!