Monday, August 3, 2009

INTEGER = NUMBER(38)

I noticed something a little strange today.

I've recently been creating tables with as specific a data type as I could. In that regard, I've been using INTEGER.
CREATE TABLE t
(
x INTEGER
);
Do a describe on that table:
CJUSTICE@TESTING>@DESC T
Name Null? Type
-------------------------------------- -------- ------------
X NUMBER(38)
Running a query on USER_TAB_COLUMNS results in this:
SELECT
data_type,
data_type_mod,
data_length,
data_precision,
data_scale,
default_length
FROM user_tab_columns
WHERE table_name = 'T'
AND column_name = 'X';

DATA_TYPE DAT DATA_LENGTH DATA_PRECISION DATA_SCALE DEFAULT_LENGTH
---------- --- ----------- -------------- ---------- --------------
NUMBER 22 0
The describe says NUMBER(38) yet USER_TAB_COLUMNS says NUMBER(22). Weird.

When I view the object through the SQL Developer schema browser and go to the SQL tab I get this:
CREATE TABLE "CJUSTICE"."T" 
(
"X" NUMBER(*,0)
);
So Oracle converts it somewhere along the way?

Further, going to the source of DBA_TAB_COLUMNS took me to DBA_TAB_COLS, I really thought it was the other way around, but I digress. I began to look at the source from DBA_TAB_COLS which had many staring at all of the SYS tables. OBJ$. COL$. HIST_HEAD$. USER$. COLTYPE$. I then started to try to unravel that...but I stopped.

It obviously won't stop me from doing my work, I just found it interesting. It's similar to A NULL Observation, III.

5 comments:

oraclenerd said...

@joel

Thanks for the link. Unfortunately I no longer have metalink access. I'll see if someone can pull it up for me though.

chet

Coskan Gundogar said...

@Chet I dont know why it is 38 but I dont get the same result for table definition in 11G Can you also send the output of DBMS_METADATA.GET_DDL

SQL> CREATE TABLE t
2 (
3 x INTEGER
4 );

Table created.

SQL> desc t;
Name Null? Type
------------------------------- -------- ----------------------------
1 X NUMBER(38)

SQL> select dbms_metadata.get_ddl( object_type, object_name, owner )
2 from all_objects where object_name='T' ;

DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
---------------------------------------------------------------------------------
-------------------------------------------------

CREATE TABLE "HR"."T"
( "X" NUMBER(38,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"




SQL> /

DATA_TYPE DAT DATA_LENGTH DATA_PRECISION DATA_SCALE DEFAULT_LENGTH
---------- --- ----------- -------------- ---------- --------------
NUMBER 22 38 0

oraclenerd said...

@coskan

I suppose version and stuff would have been important huh? ;)

From 10.2.0.1:

CJUSTICE@TESTING>SELECT * FROM v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

CJUSTICE@TESTING>SELECT dbms_metadata.get_ddl( object_type, object_name, owner )
2 FROM all_objects
3 WHERE object_name = 'T';

DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
--------------------------------------------------------------------------------

CREATE TABLE "CJUSTICE"."T"
( "X" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

From 10.2.0.3:
CJUSTICE@ORASTAGE>SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

CJUSTICE@ORASTAGE>CREATE TABLE T ( X INTEGER );

Table created.

Elapsed: 00:00:00.04
CJUSTICE@ORASTAGE>SELECT dbms_metadata.get_ddl( object_type, object_name, owner ) l
2 FROM all_objects
3 WHERE object_name = 'T';

L
--------------------------------------------------------------------------------

CREATE TABLE "CJUSTICE"."T"
( "X" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

oraclenerd said...

@coskan

From 11.1.0.6

SQL> SELECT * FROM v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production


SQL> SELECT dbms_metadata.get_ddl( object_type, object_name, owner ) l
2 FROM all_objects
3 WHERE object_name = 'T';

L
--------------------------------------------------------------------------------

CREATE TABLE "CJUSTICE"."T"
( "X" NUMBER(38,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

Coskan Gundogar said...

@Chet
behaviour looks same on 10.2.0.4 as well :) Interesting finding.

SQL> select dbms_metadata.get_ddl( object_type, object_name, owner )
2 from all_objects where object_name='T' ;

DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
------------------------------------------------------------------------
-------------------------------------------------

CREATE TABLE "EYDBA"."T"
( "X" NUMBER(*,0)
) ....