Clever Geek Handbook

Oracle ResultSetMetaData getPrecision / getScale

I am using Oracle JDBC thin driver (10.2.0.3) to connect to Oracle 10g database. I would like to get information about the database columns, so I am using ResultSetMetaData . The most important information I need is the column type and length, so I use the getColumnType , getPrecision and getScale methods.

It works for a simple query ( select * from tablename

) if the column type is "simple" eg VARCHAR2(50), NUMBER(5), NUMBER(6,2)

. If I have a more complex query ( select count(*) from tablename

) or a view based query that contains some complex calculation, the methods give weird results like:

  • getScale

    : -127
  • both getPrecision

    and getScale

    are equal to 0
  • getPrecision

    : -1

Setting the connection property oracle.jdbc.J2EE13Compliant

to true

(as suggested by several web pages) fixes getScale = -127, but still returns a 0/0 result.

Most likely I need to create a workaround for these strange results, but first I need at least complete documentation on Oracle ResultSetMetaData behavior. For example, a huge table with getPrecision / getScale for all types of SQL would be great. Is there such documentation somewhere?

+2


source to share


6 answers


An alternative is to query user_tab_columns

or all_tab_columns

.



+1


source


Valid scale 0: NUMBER (5) is the same as NUMBER (5.0)



The precision, however, must be an integer between 1 and 38 when specified. When it is undefined, as in NUMBER, the driver must return something, since it cannot return null. In this case, the driver chooses to return 0.

+1


source


It seems to me that there is no complete documentation on ResultSetMetaData. The Oracle® Database JgBC 10g Release 2 (10.2) and 11g Release 2 (11.2) Developer Guide and Developer Reference gives an example about the column name and type here , they are not relevant to other aspects.

Someone had a similar problem with PostgreSQL a few years ago and made a patch. Perhaps Oracle is using the same codebase here.

You can try to use ojdbc14 _g .jar instead of ojdbc14.jar as its classes were compiled with "javac -g" and contain some trace information.

You can also try newer drivers .

+1


source


You can use rs.getBigDecimal(columnIndex)

, and from a large decimal value you can get the precision / scale values ​​for specific columns.

+1


source


Not a direct answer to your question, but the workaround you talked about:

If you only need to check or compare db schemas, then instead of ResultSetMetaData and query all tables, use Oracle schema information as described in Reverse Engineering Data Model . I used it in my utility to export such information to text

0


source


Oracle cannot return a type based on a view or count(*)

because it was not explicitly declared. Your view can return any precision or scale depending on the view shim tables.

To overcome this, you need to specify the type of request or view it like this:

select CAST (count(*) AS NUMBER(30))
0


source






More articles:


All Articles