I'm using Oracle's JDBC thin driver (10.2.0.3) for connecting to an Oracle 10g database. I'd like to get information about the database columns, so I use ResultSetMetaData. The most important information I need is the type of the column and the length, so I use getColumnType, getPrecision, and getScale methods.
It works for a simple query (select * from tablename) if the column type is "simple" like VARCHAR2(50), NUMBER(5), NUMBER(6,2). If I have a more complex query (select count(*) from tablename) or a query based on a view which contains some complex calculation, the methods give weird results like:
getScale: -127- both
getPrecision and getScale is 0
getPrecision: -1
Setting the oracle.jdbc.J2EE13Compliant connection property to true (as suggested by several webpages) eliminates getScale=-127 but still returns 0/0 result.
Most likely I have to create a workaround for these weird results, but first I need at least a comprehensive documentation about the behavior of Oracle's ResultSetMetaData. For instance a huge table with the meaning of getPrecision/getScale for all the SQL types would be great. Is there a documentation like this somewhere?
Oracle can not return a type based on a view or the count(*) as it was not explicitly declared. Your view could return any precision or scale depending on the underlaying tables of the view.
To overcome this you would need to cast the type in your query or view like this:
select CAST (count(*) AS NUMBER(30))
An alternative is to query user_tab_columns or all_tab_columns.
A scale of 0 is acceptable: a NUMBER(5) is the same as a NUMBER(5,0)
The precision however must be an integer between 1 and 38 when it is defined. When it is undefined, as in NUMBER, the driver has to return something since it cannot return null. In that case the driver chooses to return 0.
It seems to me that there is no comprehensive documentation on ResultSetMetaData.
Oracle® Database JDBC Developer's Guide and Reference 10g Release 2 (10.2) and 11g Release 2 (11.2) gives example about column name and type here, they does not deal with other aspects.
Someone had a similar problem with PostgreSQL years ago and he made a patch. Maybe Oracle uses the same codebase here.
You may try to use the ojdbc14_g.jar instead of ojdbc14.jar as its classes were compiled with "javac -g" and contain some tracing information.
You may also try newer drivers.
You can use the rs.getBigDecimal(columnIndex) and from the big decimal you can get the precision / scale values of the specific columns.
Not a direct reply to your question, but a workaround you mentioned:
If all you have to do is check or compare db schemas, then instead of ResultSetMetaData and querying all the tables use Oracle schema information as described in Reverse Engineering a Data Model. I used it in my utility to export such information to text
