This discussion is archived
1 Reply Latest reply: Feb 25, 2013 12:44 AM by Mihael RSS

How to Store 39 digit number in database

653893 Newbie
Currently Being Moderated
I am working on a product for managing IPv6 Addresses. We convert the 128 bit IPv6 Address in digits for search functionality. Oracle database has max limit of 38 (e.g. NUMBER(38,2)). My number turns out to be of 39 digits. How can i store my number in the database.

I tried one thing:

While creating table, I did not provide any limit (e.g. NUMBER) After that I am able to store my number (I have read that max size of NUMBER data type is 21 bytes. is it correct?). Please suggest me if my approach is correct and it will not cause any issue, in indexing, and manipulation.

Regards
  • 1. Re: How to Store 39 digit number in database
    Mihael Pro
    Currently Being Moderated
    SQL> create table t1 (n number);
    
    SQL> insert into t1 values (power(10,38)+1234567);
    SQL> insert into t1 values (power(10,39)+1234567);   <- this is max
    SQL> insert into t1 values (power(10,40)+1234567);
    
    SQL> select n from t1;
    1.00000000000000000000000000000001234567e38
    1.00000000000000000000000000000000123457e39
    1.00000000000000000000000000000000012346e40    <- last digit is lost
    as ipv6 is in hex format, you can also use RAW data type that has limit of 2000 bytes
    when inserting data, use hextoraw() function

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points