Reliable way to detect an INTEGER column using JDBC (Oracle 19c)

Question

In my Oracle 19c environment, the JDBC driver returns java.sql.Types.NUMERIC as the column type for a column which was created as INTEGER. The JDBC metadata indicates a precision of 38 and scale 0.

Is checking for precision == 38 and scale == 0 the only way to detect that a column was possibly created as INTEGER?

(using standard JDBC metadata, no vendor-specific JDBC metadata extensions)

---

Related: Oracle DB returning negative scale and 0 precision using JDBC

Answer 1

Summary: Yes, look for java.sql.Types.NUMERIC with precision == 38 and scale == 0 as there is no INTEGER data type; within the database INTEGER is an alias to the NUMBER data type with a scale of 0 and maximum precision (38).

---

In Oracle databases, there is no separate INTEGER data type. INTEGER is an alias for NUMBER(38)

This is documented in the SQL Data Types documentation:

Table 6-2 ANSI Data Types Converted to Oracle Data Types

ANSI SQL Data Type	Oracle Data Type
INTEGER INT SMALLINT	NUMBER(38)

So if you set a column to the ANSI data type INTEGER then behind the scenes Oracle will actually set the column to NUMBER(38).

In practice, the data-type used appears to be NUMBER(*,0) but, from JDBC's point-of-view, a precision of * will have the maximum precision of 38.

---

CREATE TABLE table_name (a INTEGER, b NUMBER(*, 0), c NUMBER(38));

Then:

SELECT column_name, data_type, data_length, data_precision, data_scale
FROM   user_tab_cols
WHERE  table_name = 'TABLE_NAME';

Outputs:

COLUMN_NAME	DATA_TYPE	DATA_LENGTH	DATA_PRECISION	DATA_SCALE
A	NUMBER	22	null	0
B	NUMBER	22	null	0
C	NUMBER	22	38	0

and:

SELECT dbms_metadata.get_ddl('TABLE', 'TABLE_NAME', USER)
FROM   DUAL;

Outputs:

DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME',USER)
CREATE TABLE "SCHEMA_NAME"."TABLE_NAME" ( "A" NUMBER(*,0), "B" NUMBER(*,0), "C" NUMBER(38,0) ) SEGMENT CREATION DEFERRED   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING   TABLESPACE "USERS"

So even in the database's meta-data there is no way to tell that someone used INTEGER compared to NUMBER as they are all stored as NUMBER.

fiddle