subtype INTEGER [message #378424] |
Tue, 30 December 2008 06:50  |
tahpush
Messages: 961 Registered: August 2006 Location: Stockholm/Sweden
|
Senior Member |

|
|
I got a bit annoyed, so it's either me or the Oracle Documentaion.
SQL>
create table NUMBERS(col_int integer,col_numb number(38,0));
Table created.
So the ansi-datatype integer is implicitely converted to
oracle datatype number
SQL> desc numbers;
Name Null? Type
------------- -------- -------------
COL_INT NUMBER(38)
COL_NUMB NUMBER(38)
Per oracle documentation
STANDARD sub-types defined in Oracle 10g
subtype INTEGER is NUMBER(38,0);
SQL> select column_name,data_length,
data_precision,data_scale
from user_tab_columns where table_name = 'NUMBERS';
COLUMN_NAME DATA_LENGTH DATA_PRECISION DATA_SCALE
--------------- ----------- -------------- ----------
COL_INT 22 0
COL_NUMB 22 38 0
DATA_PRECISION is used to constrain input values and INTEGER is a constrained subtype of number.
Therefor I find it a bit strange that DATA_PRECISION column doesn't display 38 for COL_INT as well. ?
The same goes for smallint etc.
Or should I get some sleep
[Updated on: Tue, 30 December 2008 06:52] Report message to a moderator
|
|
|
Re: subtype INTEGER [message #378427 is a reply to message #378424] |
Tue, 30 December 2008 06:58   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
SQL> create table a (b integer, c number(38,0));
Table created.
SQL> select column_name,data_length,
2 data_precision,data_scale
3 from user_tab_columns where table_name = 'A';
COLUMN_NAME DATA_LENGTH DATA_PRECISION DATA_SCALE
------------------------------ ----------- -------------- ----------
B 22 0
C 22 38 0
SQL> insert into a values (2.4, 2.5);
1 row created.
SQL> select * from a;
B C
---------- ----------
2 3
3 2
End-result seems to be ok.
|
|
|
|
|
Re: subtype INTEGER [message #378438 is a reply to message #378424] |
Tue, 30 December 2008 07:47   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
And then?
NULL means unscontrained that is 38 in the current versions. That's all.
You get the same thing if you declare your col_numb as "number(*,0)".
Regards
Michel
[Updated on: Tue, 30 December 2008 07:51] Report message to a moderator
|
|
|
Re: subtype INTEGER [message #378445 is a reply to message #378438] |
Tue, 30 December 2008 08:08  |
tahpush
Messages: 961 Registered: August 2006 Location: Stockholm/Sweden
|
Senior Member |

|
|
Thanks for the replies
Quote: | NULL means unscontrained that is 38 in the current versions. That's all
|
Agree 100%, cant be anything else
But what confused me was that
Per oracle documentation
STANDARD sub-types defined in Oracle 10g
subtype INTEGER is NUMBER(38,0);
Which I thought would give a DATA_PRECISION of 38 instead of "null"
[Updated on: Tue, 30 December 2008 08:09] Report message to a moderator
|
|
|