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='

---------------------------------------- ----------- ---------- --------------
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:


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?

1 Answer 1


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;


   (    "A" NUMBER(*,0)


(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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.