Home » SQL & PL/SQL » SQL & PL/SQL » subtype INTEGER (10.2.0.4)
subtype INTEGER [message #378424] Tue, 30 December 2008 06:50 Go to next message
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 Smile

[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 Go to previous messageGo to next message
Frank
Messages: 7880
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 #378428 is a reply to message #378427] Tue, 30 December 2008 07:01 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Yes Frank

Good for you, that works for me as well Smile
But that wasn't the question
Re: subtype INTEGER [message #378430 is a reply to message #378428] Tue, 30 December 2008 07:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hm, must have misunderstood you then..
Re: subtype INTEGER [message #378438 is a reply to message #378424] Tue, 30 December 2008 07:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous message
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

Previous Topic: Updating Records
Next Topic: cannot perform fetch on a pl sql statement
Goto Forum:
  


Current Time: Fri Dec 09 21:11:44 CST 2016

Total time taken to generate the page: 0.13421 seconds