Oracle integer to number conversion

According to the Oracle documentation, creating a table with a column of ANSI type INT, Oracle will convert it to NUMBER(38). According to the same document, NUMBER(38) is supposed to be a NUMBER with a precision of 38 and a scale of 0. But in practice, I am seeing a NUMBER with data_precision set to NULL and a data_length set to 22.

SQL> create table ltheisen.silly ( id int );

Table created.

SQL> select substr(data_type,1,10), data_length, data_scale, data_precision from
 all_tab_columns where owner='LTHEISEN' and table_name='SILLY' and column_name='
ID';

SUBSTR(DATA_TYPE,1,10)                   DATA_LENGTH DATA_SCALE DATA_PRECISION
---------------------------------------- ----------- ---------- --------------
NUMBER                                            22          0

What gives?

Also, I am trying to do a generic conversion from Oracle to ANSI. Is there any way to determine from the metadata that the NUMBER was created as, or fits into an INT?

Lastly, I tried creating with BINARY_INTEGER which appears in the documentation, but the create statement fails:

SQL> create table ltheisen.silly ( id binary_integer );
create table ltheisen.silly ( id binary_integer )
                                 *
ERROR at line 1:
ORA-00902: invalid datatype

What am I missing?

-------------------- UPDATE -----------------------------

Reading a little deeper gives this gem:


Specify a floating-point number using the following form:


NUMBER

The absence of precision and scale designators specifies the maximum range and precision 
for an Oracle number.

So since the datatype is NUMBER and there is no precision specified, we can assume it is maximum precision?

A DATA_LENGTH of 22 is just the largest number of bytes it can take to store the largest possible number (Oracle uses 2 digits per byte). A NULL DATA_PRECISION implies the maximum of 38.

You can verify this with DBMS_METADATA:

SQL>  create table sotmp ( a int );

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','SOTMP')
  2  from dual;

DBMS_METADATA.GET_DDL('TABLE','SOTMP')
--------------------------------------------------------------------------------

  CREATE TABLE "PHIL"."SOTMP"
   (    "A" NUMBER(*,0)
   ) SEGMENT CREATION DEFER

SQL>

(The "*" in the above output denotes the maximum precision).

BINARY_INTEGER is not a data type that can be used in table columns. It can only be used as a PL/SQL variable datatype.

StackExchange Database Administrators Q#23753, answer score: 8