Home » SQL & PL/SQL » SQL & PL/SQL » Integer Vs Number Datatype (11.1.0.7.0)
Integer Vs Number Datatype [message #432349] Mon, 23 November 2009 14:27 Go to next message
faiz_hyd
Messages: 294
Registered: February 2006
Senior Member
Hi,
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> create table azz( a integer,b number);

Table created.

SQL> desc azz
 Name        Null?    Type
 ---------- -------- ------------
 A                    NUMBER(38)
 B                    NUMBER

SQL> select a.TABLE_NAME,b.column_id,b.COLUMN_NAME,b.DATA_TYPE,b.DATA_LENGTH,b.NULLABLE  from dba_tables a,dba_tab_columns b
 where a.TABLE_NAME=b.TABLE_NAME
and a.TABLE_NAME in ('AZZ')
group by a.table_name,b.column_id,b.COLUMN_NAME,b.DATA_TYPE,b.DATA_LENGTH,b.NULLABLE
order by a.table_name ASC,b.COLUMN_ID;

TABLE_NAME    COLUMN_ID COLUMN_NAME DATA_TYPE   DATA_LENGTH  
----------------------------------------------------------------

AZZ         1          A       NUMBER 22 

AZZ         2          B       NUMBER 22 



Is this normal that we are creating with Integer and it creates with number(22) but in Desc Table Command it shows Number(38).

Thanks

Abdul.
Re: Integer Vs Number Datatype [message #432351 is a reply to message #432349] Mon, 23 November 2009 14:57 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
Senior Member
22 is LENGTH
38 is PRECISION
different units of measure

Re: Integer Vs Number Datatype [message #432352 is a reply to message #432351] Mon, 23 November 2009 15:49 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
To visualize what are the length and precision:
1234.5678 - 8 digits long, 4 digits of precision after decimal separator
12.345678 - 8 digits long, 6 digits of precision after decimal separator
000056.78 - 8 digits long, 2 digits of precision after decimal separator

Probably some people would now point out that the digits are different when you go to binary. Just for them let assume that the parts before comma are 0x1234,0x12,0x56 and that situation is similar for the fractional part which is in fact more complex.
Re: Integer Vs Number Datatype [message #432527 is a reply to message #432349] Tue, 24 November 2009 16:46 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Instead of your first sql

select a.TABLE_NAME,b.column_id,b.COLUMN_NAME,b.DATA_TYPE,b.DATA_LENGTH,b.NULLABLE
from dba_tables a,dba_tab_columns b
where a.TABLE_NAME=b.TABLE_NAME
and a.TABLE_NAME in ('AZZ')
group by a.table_name,b.column_id,b.COLUMN_NAME,b.DATA_TYPE,b.DATA_LENGTH,b.NULLABLE
order by a.table_name ASC,b.COLUMN_ID;

try this one

select a.TABLE_NAME,b.column_id,b.COLUMN_NAME,b.DATA_TYPE,b.DATA_PRECISION,b.NULLABLE
from dba_tables a,dba_tab_columns b
where a.TABLE_NAME=b.TABLE_NAME
and a.TABLE_NAME in ('AZZ')
group by a.table_name,b.column_id,b.COLUMN_NAME,b.DATA_TYPE,b.DATA_PRECISION,b.NULLABLE
order by a.table_name ASC,b.COLUMN_ID;


As was indicated, data_precision is what is used b desc.

Kevin
Re: Integer Vs Number Datatype [message #432551 is a reply to message #432527] Tue, 24 November 2009 21:53 Go to previous message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
1) This will also help you
2) this too By tom

Regards
Sriram Smile

[Updated on: Tue, 24 November 2009 21:57]

Report message to a moderator

Previous Topic: Error Executing Package/Procedure
Next Topic: how to get percentage on the row
Goto Forum:
  


Current Time: Sun Sep 25 16:17:07 CDT 2016

Total time taken to generate the page: 0.10838 seconds