Oracle NUMBER Data Types

Introduction: This is my study about number data types present
in Oracle database. I have used Oracle 10g (10.2.0.3) for my study:-

Data types INT, INTEGER and SMALLINT all uses NUMBER in Oracle, let us see how:-

Example # 1:

drop table tab1 purge;

create table tab2 (
I INT,
S SMALLINT,
IG INTEGER);

describe tab1

Name Null? Type
------------------ -------- ----------
I NUMBER(38)
S NUMBER(38)
IG NUMBER(38)

Example # 2: Let's see about other number data types:-

drop table tab2 purge;

create table tab2 (
X number,
NUM1 NUMBER,
FLOAT1 FLOAT,
BFLOAT BINARY_FLOAT,
BDOUBLE BINARY_DOUBLE);

describe tab2

Name Null? Type
----------------------- -------- -------------
X NUMBER
NUM1 NUMBER
FLOAT1 FLOAT(126)
BFLOAT BINARY_FLOAT
BDOUBLE BINARY_DOUBLE

declare
y number;
begin for a in 1..10 loop
y:=1/(a*100);
insert into tab2 values (a,y,y,y,y);
end loop;
commit;
end;
/

col NUM1 for .99999999999999999999
col FLOAT1 for .99999999999999999999
col BFLOAT for .99999999999999999999
col BDOUBLE for .99999999999999999999
set lines 120

select * from tab2;

X NUM1 FLOAT1 BFLOAT BDOUBLE
-- ---------------------- ---------------------- ---------------------- ------------
1 .01000000000000000000 .01000000000000000000 .00999999978000000000 .01000000000000000000 2 .00500000000000000000 .00500000000000000000 .00499999989000000000 .00500000000000000010 3 .00333333333333333333 .00333333333333333333 .00333333341000000000 .00333333333333333350 4 .00250000000000000000 .00250000000000000000 .00249999994000000000 .00250000000000000010 5 .00200000000000000000 .00200000000000000000 .00200000009000000000 .00200000000000000000 6 .00166666666666666667 .00166666666666666667 .00166666671000000000 .00166666666666666680 7 .00142857142857142857 .00142857142857142857 .00142857141000000000 .00142857142857142860 8 .00125000000000000000 .00125000000000000000 .00124999997000000000 .00125000000000000000 9 .00111111111111111111 .00111111111111111111 .00111111114000000000 .00111111111111111110 10 .00100000000000000000 .00100000000000000000 .00100000005000000000 .00100000000000000000
10 rows selected.

In the above, the NUM1 (which is using Number data type) and FLOAT1 (which is using FLOAT data type) preserves accuracy/scale than others.

Example # 3: Now, lets us try this

delete tab2;

declare
y number;
begin for a in 1..10 loop
y:=(a+(sqrt(a))/(a*100));
insert into tab2 values (a,y,y,y,y);
end loop;
commit;
end;
/

col NUM1 for 99.99999999999999999999999999999999999999
col FLOAT1 for 99.99999999999999999999999999999999999999

select NUM1,FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 1.01000000000000000000000000000000000000 1.01000000000000000000000000000000000000
2.00707106781186547524400844362104849039 2.00707106781186547524400844362104849040
3.00577350269189625764509148780501957456 3.00577350269189625764509148780501957460
4.00500000000000000000000000000000000000 4.00500000000000000000000000000000000000
5.00447213595499957939281834733746255247 5.00447213595499957939281834733746255250
6.00408248290463863016366214012450981899 6.00408248290463863016366214012450981900
7.00377964473009227227214516536234180061 7.00377964473009227227214516536234180060
8.00353553390593273762200422181052424520 8.00353553390593273762200422181052424520
9.00333333333333333333333333333333333333 9.00333333333333333333333333333333333330
10.00316227766016837933199889354443271853 10.00316227766016837933199889354443271900

10 rows selected.

select avg(NUM1) NUM1, avg(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 5.50502099789929266650050620329386725341 5.50502099789929266650050620329386725346

select sum(NUM1) NUM1, sum(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 55.05020997899292666500506203293867253408 55.05020997899292666500506203293867253460

select max(NUM1) NUM1, max(FLOAT1) FLOAT1 from tab2;

NUM1 FLOAT1
------------------------------------------ ------------------------------------------ 10.00316227766016837933199889354443271853 10.00316227766016837933199889354443271900

Example # 4: Now, lets us compare NUMBER and FLOAT data type and try this

drop table tab3 purge;

create table tab3 (
NUM1 NUMBER,
FLOAT1 FLOAT);

delete tab3;

insert into tab3 values (
1234567891234567891234567890123456789123.45,
1234567891234567891234567890123456789123.45);

col NUM1 for 9999999999999999999999999999999999999999.99
col FLOAT1 for 9999999999999999999999999999999999999999.99

select * from tab3;

NUM1
--------------------------------------------
FLOAT1
--------------------------------------------
1234567891234567891234567890123456789123.00
1234567891234567891234567890123456789100.00

In the above when comparing the NUM1 (NUMBER data type) and FLOAT1 (FLOAT data type), it appears that NUM1 (Number data type) preserves accuracy/scale than the other.

When default is used, NUMBER datatype can preserve values upto 40 bytes and FLOAT upto 38 bytes

Bug in Oracle 10g while using Check Constraint

Came across this bug in the check constraint in Oracle database 10g.
This has been tested in both 32 and 64 bit Oracle 10g (10.2.0.3) running on Windows 2003 Server.

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Reproducing the bug: Create a table called STORE having filed called LOCATION, this field should accept specified values or NULL values, below is the syntax:-

SQL> CREATE TABLE STORE (LOCATION VARCHAR2(15));

Table created.

SQL> ALTER TABLE STORE
2 ADD CONSTRAINT C1 CHECK
3 (LOCATION IN ('PHILLY','HARRISBURG',NULL));

Table altered.

Bug Note: By specifying a NULL, the constraint doesn't not verifies/validates the entered value.

SQL> INSERT INTO STORE VALUES ('PHILLY');
1 row created.

SQL> INSERT INTO STORE VALUES ('philly');
1 row created.

SQL> INSERT INTO STORE VALUES ('HARRISBURG');
1 row created.

SQL> INSERT INTO STORE VALUES ('harrisburg');
1 row created.

SQL> INSERT INTO STORE VALUES (null);
1 row created.

SQL> INSERT INTO STORE VALUES ('PITTSBURGH');
1 row created.

SQL> INSERT INTO STORE VALUES ('pittsburgh');
1 row created.

SQL> commit;
Commit complete.

SQL> col SEARCH_CONDITION for a50
SQL> select
2 STATUS,
3 DEFERRABLE,
4 DEFERRED,
5 VALIDATED,
6 SEARCH_CONDITION
7 FROM ALL_CONSTRAINTS A
8 WHERE table_name='STORE';

STATUS DEFERRABLE DEFERRED VALIDATED SEARCH_CONDITION
-------- -------------- --------- ------------- ----------------------------------------
ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED LOCATION IN ('PHILLY','HARRISBURG',NULL)

SQL> select LOCATION from STORE;
LOCATION
---------------
PHILLY
philly
HARRISBURG
harrisburg

PITTSBURGH
pittsburgh

7 rows selected.

PROBLEM: DMLs like INSERT, UPDATE and DELETE works, but Query (SELECT) statements doesn't work in Oracle 10g (Tested in 10.2.0.3 both in 32 and 64 bit) while querying for records that actually violates the constraint rule. But works fine in Oracle 9i (tested in 9.2.0.8) and 11g (tested in 11.1.0.6)

Condition that violate the constraint rule:-
SQL> select count(*) from STORE where LOCATION='philly';

COUNT(*)
----------
0

Condition that doesn't violate the constraint rule:-
SQL> select count(*) from STORE where LOCATION='PHILLY';
COUNT(*)
----------
1

SQL> select count(*) from STORE where upper(LOCATION)=upper('philly');
COUNT(*)
----------
2

Condition that violate the constraint rule, DMLs works fine:-
SQL> update STORE set LOCATION='XYX' where LOCATION='philly';
1 row updated.

SQL> commit;
Commit complete.

Condition that violate the constraint rule:-
SQL> select count(*) from STORE where LOCATION='XYX';
COUNT(*)
----------
0

SQL> select LOCATION from STORE;
LOCATION
---------------
PHILLY
XYX
HARRISBURG
harrisburg

PITTSBURGH
pittsburgh

7 rows selected.

Condition that violate the constraint rule, DMLs works fine:-
SQL> delete from STORE where LOCATION='XYX';

1 row deleted.

SQL> select LOCATION from STORE;
LOCATION
---------------
PHILLY
HARRISBURG
harrisburg

PITTSBURGH
pittsburgh

6 rows selected.

SQL> commit;

Commit complete.

FIX for 10g

ALTER TABLE STORE drop CONSTRAINT C1 ;

Option # 1: Create a check constraint without specifying NULL. By default check constraint will accept NULL values, hence it doesn't have to be mentioned in the constraint condition

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(LOCATION IN ('PHILLY','HARRISBURG'));

or

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(LOCATION IN ('PHILLY','HARRISBURG') or LOCATION IS NULL);

Option # 2: Specify function like UPPER or LOWER in the constraint

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(UPPER(LOCATION) IN ('PHILLY','HARRISBURG'));

Option # 3: If NOT NULL need to be enforced along with the above condition, then use the following:-

ALTER TABLE STORE
ADD CONSTRAINT C1 CHECK
(LOCATION IN ('PHILLY','HARRISBURG') and LOCATION IS NOT NULL);

Did anyone else had similar problem?