Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Amar.

Asked: February 27, 2017 - 9:55 am UTC

Last updated: February 28, 2017 - 3:10 pm UTC

Version: Oracle 11g R2

Viewed 1000+ times

You Asked

Hi Guys,

I tried to execute the below script, but got some unambiguous result while fetching the record.
I am trying this query in Windows 7 Operating system.

SELECT * FROM V$Version;
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

CREATE TABLE TBL (RNO NUMBER,NO NUMBER);

TRUNCATE TABLE tbl;

INSERT INTO TBL VALUES
(95780971304118053647396689196894323976100000000000000,1);

INSERT INTO TBL VALUES
(99999999999999999999999999999999999999999,2);


SELECT * FROM TBL;

>
RNO NO
----------------------------------------------------------
95780971304118053647396689196894323976100000000000000 1
100000000000000000000000000000000000000000 2

Would you please elaborate why is it so?
As in first case it stores and displays the same input while in second one it treats it as 100000000000000000000000000000000000000000.
Is there any different internal storage mechanism for these numbers.

Please acknowledge .

Thanks,
Amar Borishaha.

and Chris said...

Oracle Database uses up to 22 bytes to store numbers.

The first byte is for the column length.
The second is for the sign and exponent.

This leaves 20 bytes to store numbers. Oracle can store two decimal digits per byte. So you can have up to 40 digits of precision.

You've not specified a precision. So you can store up to this maximum of 40.

When you try and insert a number with more digits of precision than this, the database rounds it. Although your first number has 53 digits, only the first 40 of these are significant. So you can store this as:
9.57809713041180536473966891968943239761e52

Thus there's no need for rounding. But the second has 42 digits of significance. So Oracle rounds it up:

drop table tbl purge;
CREATE TABLE TBL (RNO NUMBER,NO NUMBER);

INSERT INTO TBL VALUES (99999999999999999999999999999999999999, 1);
INSERT INTO TBL VALUES (999999999999999999999999999999999999999, 2);
INSERT INTO TBL VALUES (9999999999999999999999999999999999999999, 3);
INSERT INTO TBL VALUES (99999999999999999999999999999999999999999, 4);

INSERT INTO TBL VALUES (-99999999999999999999999999999999999999, 5);
INSERT INTO TBL VALUES (-999999999999999999999999999999999999999, 6);
INSERT INTO TBL VALUES (-9999999999999999999999999999999999999999, 7);
INSERT INTO TBL VALUES (-99999999999999999999999999999999999999999, 8);
commit;

select no, rno, dump(rno) from tbl;

NO  RNO                                                       DUMP(RNO)                                                                                          
1   99,999,999,999,999,999,999,999,999,999,999,999,999        Typ=2 Len=20: 211,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100      
2   999,999,999,999,999,999,999,999,999,999,999,999,999       Typ=2 Len=21: 212,10,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100   
3   9,999,999,999,999,999,999,999,999,999,999,999,999,999     Typ=2 Len=21: 212,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100,100  
4   100,000,000,000,000,000,000,000,000,000,000,000,000,000   Typ=2 Len=2: 213,11                                                                                
5   -99,999,999,999,999,999,999,999,999,999,999,999,999       Typ=2 Len=21: 44,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,102                                         
6   -999,999,999,999,999,999,999,999,999,999,999,999,999      Typ=2 Len=21: 43,92,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2                                          
7   -9,999,999,999,999,999,999,999,999,999,999,999,999,999    Typ=2 Len=21: 43,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2,2                                           
8   -100,000,000,000,000,000,000,000,000,000,000,000,000,000  Typ=2 Len=3: 42,91,102


http://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF00222

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for reply Chris

Amar Borishaha, February 28, 2017 - 7:44 am UTC

Even Chris, while doing mathematical / comparison operation on such huge numbers
would result into some ambiguity.

DECLARE
V_NUM1 NUMBER;
V_NUM2 NUMBER;
v_result NUMBER;
BEGIN
IF 95780971304118053647396689196894323976100000000000000 = 95780971304118053647396689196894323976100000000000001
THEN
DBMS_OUTPUT.PUT_LINE('BOTH ARE EQUAL');
ELSE
DBMS_OUTPUT.PUT_LINE('BOTH ARE NOT EQUAL');
END IF;

V_RESULT := 95780971304118053647396689196894323976100000000000001 - 95780971304118053647396689196894323976100000000000000;

DBMS_OUTPUT.put_line('Result : '||v_result);

V_NUM1 := 95780971304118053647396689196894323976100000000000000 + 100;
v_num2 := 95780971304118053647396689196894323976100000000000000 - 100;

DBMS_OUTPUT.PUT_LINE(' V_NUM1 : '||V_NUM1||CHR(13)||' V_NUM2 : '||V_NUM2);

IF V_NUM1 = V_NUM2
THEN
DBMS_OUTPUT.PUT_LINE('BOTH V_NUM1 and V_NUM2 ARE EQUAL');
ELSE
DBMS_OUTPUT.PUT_LINE('BOTH V_NUM1 and V_NUM2 ARE NOT EQUAL');
END IF;

END;
/
----------------------------------------------------------------------------
BOTH ARE EQUAL
Result : 0
V_NUM1 : 95780971304118053647396689196894323976100000000000000
V_NUM2 : 95780971304118053647396689196894323976100000000000000
BOTH V_NUM1 and V_NUM2 ARE EQUAL


Is it something bug in Number Interpretation or can we say 95780971304118053647396689196894323976100000000000000 is the maximum value
up to which we can rely any mathematical / comparison operation in Oracle for variable of Number Datatype(without Precision and scale).
Chris Saxon
February 28, 2017 - 3:09 pm UTC

I'm not sure where you get 95780971304118053647396689196894323976100000000000000 as a maximum value?

Once you're dealing with numbers with >= 40 digits of precision then you may have some loss of accuracy due to rounding:

DECLARE 
V_NUM1 NUMBER := 9.99999999999999999999999999999999999e39; 
V_NUM2 NUMBER := v_num1 + 1; 
v_result NUMBER; 
BEGIN 
IF V_NUM1 = V_NUM2
THEN 
DBMS_OUTPUT.PUT_LINE('BOTH ARE EQUAL'); 
ELSE 
DBMS_OUTPUT.PUT_LINE('BOTH ARE NOT EQUAL'); 
END IF; 

V_RESULT := V_NUM1 - V_NUM2; 

DBMS_OUTPUT.put_line('Result : '||v_result); 

V_NUM1 := V_NUM1 + 1; 
v_num2 := V_NUM1 - 1; 

DBMS_OUTPUT.PUT_LINE(' V_NUM1 : '||V_NUM1||CHR(13)||' V_NUM2 : '||V_NUM2); 

IF V_NUM1 = V_NUM2 
THEN 
DBMS_OUTPUT.PUT_LINE('BOTH V_NUM1 and V_NUM2 ARE EQUAL'); 
ELSE 
DBMS_OUTPUT.PUT_LINE('BOTH V_NUM1 and V_NUM2 ARE NOT EQUAL'); 
END IF; 

END; 
/ 

BOTH ARE NOT EQUAL
Result : -1
 V_NUM1 : 9999999999999999999999999999999999990001
 V_NUM2 : 9999999999999999999999999999999999990000
BOTH V_NUM1 and V_NUM2 ARE NOT EQUAL

DECLARE 
V_NUM1 NUMBER := 9.99999999999999999999999999999999999e40; 
V_NUM2 NUMBER := v_num1 + 1; 
v_result NUMBER; 
BEGIN 
IF V_NUM1 = V_NUM2
THEN 
DBMS_OUTPUT.PUT_LINE('BOTH ARE EQUAL'); 
ELSE 
DBMS_OUTPUT.PUT_LINE('BOTH ARE NOT EQUAL'); 
END IF; 

V_RESULT := V_NUM1 - V_NUM2; 

DBMS_OUTPUT.put_line('Result : '||v_result); 

V_NUM1 := V_NUM1 + 1; 
v_num2 := V_NUM1 - 1; 

DBMS_OUTPUT.PUT_LINE(' V_NUM1 : '||V_NUM1||CHR(13)||' V_NUM2 : '||V_NUM2); 

IF V_NUM1 = V_NUM2 
THEN 
DBMS_OUTPUT.PUT_LINE('BOTH V_NUM1 and V_NUM2 ARE EQUAL'); 
ELSE 
DBMS_OUTPUT.PUT_LINE('BOTH V_NUM1 and V_NUM2 ARE NOT EQUAL'); 
END IF; 

END; 
/ 

BOTH ARE EQUAL
Result : 0
 V_NUM1 : 99999999999999999999999999999999999900000
 V_NUM2 : 99999999999999999999999999999999999900000
BOTH V_NUM1 and V_NUM2 ARE EQUAL

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.