Skip to Main Content
  • Questions
  • Convert arbitrary length numbers from decimal to binary to decimal in PLSQL

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Manoj.

Asked: April 07, 2020 - 6:47 am UTC

Answered by: Connor McDonald - Last updated: April 09, 2020 - 5:55 am UTC

Category: PL/SQL - Version: 11g

Viewed 1000+ times

You Asked

Hi,
We are storing a 100 digit length number in a varchar2 field in oracle database table column.
In PLSQL code, we need to convert this 100 digit number to its binary equivalent (300+ digits), add some more digits to binary and then convert this back to decimal and store in the DB again.

The number field only has 38 digit support. How do we perform this conversion in PLSQL ?
Is there a reusable API or procedure ?
Please recommend

Thanks,
Manoj

and we said...

We don't have anything natively to do that...

Presuming that the only real value here is the bits (not the decimal number) perhaps an alternative storage option would be a RAW ? Then you can use the native bitwise operations in UTL_RAW to do any bit manipulations. Anyway... if you can't do that, then we need to build our own routines. We can use nested tables (arrays) to store the digits and hence do arbitrary length arithmentic

SQL> create or replace
  2  function dec_to_bit(p_decimal varchar2) return varchar2 is
  3
  4    l_num varchar2(1000) := p_decimal;
  5    l_res varchar2(1000);
  6    l_last_dig pls_integer;
  7
  8    function div2(p_dec varchar2) return varchar2 is
  9      type   integer_array is  table of integer;
 10      n1     integer_array := integer_array();
 11      res    integer_array := integer_array();
 12      carry  pls_integer := 0;
 13      tmp    pls_integer;
 14      str    varchar2(1000);
 15    begin
 16      for i in 1 .. length(p_dec) loop
 17        n1.extend;
 18        n1(i) := to_number(substr(p_dec,i,1));
 19      end loop;
 20      res.extend(n1.count);
 21
 22      for i in 1 .. n1.count
 23      loop
 24         str := str || trunc(((10*carry)+n1(i))/2);
 25         carry := mod(n1(i),2);
 26      end loop;
 27      return ltrim(str,'0');
 28    end;
 29  begin
 30    loop
 31       l_last_dig := to_number(substr(l_num,-1,1));
 32       l_res := to_char(mod(l_last_dig,2)) || l_res;
 33       l_num := div2(l_num);
 34       exit when l_num is null;
 35    end loop;
 36    return l_res;
 37  end;
 38  /

Function created.

SQL> select dec_to_bit(1232123123109837192837918273918273) from dual;

DEC_TO_BIT(1232123123109837192837918273918273)
----------------------------------------------------------------------------------------------------------------------------------
11110010111111100101001000011110110110111110011000000110000100011110100111011010001001000001010101010101000001

1 row selected.

SQL>


and then another routine to handle reverse. I've capped this one at 2^512, so 512 bits is your max.

SQL> create or replace
  2  function bit_to_dec(p_bitstr varchar2) return varchar2 is
  3    type integer_array is  table of number;
  4    type powers_of_two is table of integer_array;
  5    p powers_of_two := powers_of_two();
  6    cumtot integer_array := integer_array();
  7    l_str varchar2(512);
  8      flag boolean := false;
  9
 10    function str_as_array(p_str varchar2) return integer_array is
 11      l_ret integer_array := integer_array();
 12    begin
 13      l_ret.extend(length(p_str)+1);
 14      l_ret(1) := 0;
 15      for i in 2 ..  l_ret.count
 16      loop
 17        l_ret(i) := to_number(substr(p_str,i-1,1));
 18      end loop;
 19      return l_ret;
 20    end;
 21
 22    procedure double(a1 integer_array, r in out integer_array) is
 23      carry pls_integer := 0;
 24      tmp pls_integer;
 25    begin
 26      r := integer_array();
 27      r.extend(a1.count);
 28      for i in reverse 1 .. a1.count
 29      loop
 30          tmp :=  a1(i)+a1(i)+carry;
 31          if tmp > 9 then
 32             carry := 1;
 33             tmp := tmp-10;
 34          else
 35             carry := 0;
 36          end if;
 37          r(i) := tmp;
 38      end loop;
 39    end;
 40
 41    procedure inc(a1 integer_array, tot in out integer_array) is
 42      l_pad    integer_array := a1;
 43      carry    pls_integer := 0;
 44      tmp      pls_integer;
 45      last_dig pls_integer := 512;
 46    begin
 47      for i in reverse 1 .. a1.count
 48      loop
 49        tmp :=  a1(i)+tot(last_dig)+carry;
 50        if tmp > 9 then
 51           carry := 1;
 52           tmp := tmp-10;
 53        else
 54           carry := 0;
 55        end if;
 56        tot(last_dig) := tmp;
 57        last_dig := last_dig - 1;
 58      end loop;
 59      tot(last_dig) := carry;
 60    end;
 61
 62  begin
 63    p.extend(512);
 64
 65    for i in 1 .. 128
 66    loop
 67      p(i) := str_as_array(to_char(power(2,i-1)));
 68    end loop;
 69    for i in 128 .. 511
 70    loop
 71      double(p(i),p(i+1));
 72    end loop;
 73
 74    cumtot.extend(512);
 75    for i in 1 .. 512
 76    loop
 77      cumtot(i) := 0;
 78    end loop;
 79
 80    for i in 1 .. length(p_bitstr)
 81    loop
 82      if substr(p_bitstr,-i,1) = '1' then
 83        inc(p(i) , cumtot);
 84      end if;
 85    end loop;
 86
 87    for i in 1 .. cumtot.count
 88    loop
 89      if cumtot(i) > 0 or flag then
 90        l_str := l_str || cumtot(i);
 91        flag := true;
 92      end if;
 93    end loop;
 94
 95    return l_str;
 96  end;
 97  /

Function created.

SQL>
SQL> select bit_to_dec('11110010111111100101001000011110110110111110011000000110000100011110100111011010001001000001010101010101000001') from dual;

BIT_TO_DEC('11110010111111100101001000011110110110111110011000000110000100011110100111011010001001000001010101010101000001')
----------------------------------------------------------------------------------------------------------------------------------
1232123123109837192837918273918273

1 row selected.

SQL>


If you are planing to use these in a real implementation, you would use a package and perform the powers of 2 initialisation once, not every time.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.