CODEWITHSUNDEEP
sqloracle-databaseoracle11g
contactmatt
contactmatt

Reputation: 18600

Oracle - Integer datatype precision max? I'm able to enter more than 38 numbers into a integer field

I have an integer column, and according to others an integer is supposed to have a precision of 38 and is basically an alias for the type delcaration of Number(38)

I'm sure I'm missing something, but how am I able to enter 128 digits into an INTEGER column?

CREATE TABLE TEST
(
  ID_INT  INTEGER                               NOT NULL
);

insert into test( id_int)
values ( '0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890129010');

Version: Oracle 11

Upvotes: 3

Views: 10219

Answers (3)

John Doyle
John Doyle

Reputation: 7793

INTEGER is actually an alias for NUMBER (which can also be written as NUMBER(*)) and not NUMBER(38). NUMBER on its own means no precision and you can store any value. The 38 is a guarantee of 38 digits of precision to allow portability between different systems running Oracle though it will happily allow numbers that are a lot higher - just don't expect it to always port correctly if you ever have to. I created a test table:

create table TESTNUM
(
  ID_INT     integer
 ,ID_NUM38   number(38)
 ,ID_NUM     number(*)
);

And here is a query to show the precisions stored:

select CNAME, COLTYPE, WIDTH, SCALE, PRECISION
  from COL
 where TNAME = 'TESTNUM';

I get back:

+----------+---------+-------+-------+-----------+
|  CNAME   | COLTYPE | WIDTH | SCALE | PRECISION |
+----------+---------+-------+-------+-----------+
| ID_INT   | NUMBER  |    22 |     0 |           |
| ID_NUM38 | NUMBER  |    22 |     0 |        38 |
| ID_NUM   | NUMBER  |    22 |       |           |
+----------+---------+-------+-------+-----------+

Upvotes: 3

tbone
tbone

Reputation: 15473

I believe precision refers to maximum number of significant digits

Not the same thing as only allowing 38 length number.

See here for explanation of significant digits.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231671

You can insert the row. But the data itself is then truncated. For example, note that the last 2 digits are lost when I query the data

SQL> insert into test( id_int )
  2    values( 123456789012345678901234567890123456789012 );

1 row created.

SQL> select id_int from test;

                                                ID_INT
------------------------------------------------------
            123456789012345678901234567890123456789000

Upvotes: 2

Related Questions