interger data type

[naushi]

When you define a column with data type integer it converts it to Number(38). does any one know how much space in numbe r of bytes does it allocate. If we insert a small value like 2 or 3. will it still occupy the entire allocated space or it works like varchar2.

will appreciate some help

Regards,
Naushi Hussain
Naushi.hussain@honeywell.com

 

[ddrillich]

Please try:

create table tst ( n number );

insert into tst values (2);
insert into tst values (200000234);
insert into tst values (20000023424000000000032412344032432423);

select n,dump from tst;

Regards,
Dan

 

[SantaMufasa]

Naushi,

In addition to Dan's informative test is:

create table tst (n integer);
SQL> desc tst
 Name                                Null?    Type
 ----------------------------------- -------- ----------
 N                                            NUMBER(38)
insert into tst values (2);
insert into tst values (20000000000000000000000000000000000000);
col a format a20 heading "Dump"
col n heading "Integer value" format 999,999,999,999,999,999,999,999,999,999,999,999,999
select n, dump(n) a from tst;
                                       Integer value Dump
---------------------------------------------------- -------------------
                                                   2 Typ=2 Len=2: 193,3
  20,000,000,000,000,000,000,000,000,000,000,000,000 Typ=2 Len=2: 211,21

Notice that a 38-digit value can occupy as little as 2 bytes since Oracle uses exponential notation to store otherwise-insignificant '0' place holders.

Mufasa
(aka Dave of Sandy, Utah, USA @ 22:35 (12Mar04) UTC (aka "GMT" and "Zulu"), 15:35 (12Mar04) Mountain Time)