1 Reply Latest reply: Feb 25, 2013 2:44 AM by Mihael RSS

    How to Store 39 digit number in database

      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.

        • 1. Re: How to Store 39 digit number in database
          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.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