• Questions
• # Convert arbitrary length numbers from decimal to binary to decimal in PLSQL

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

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 ?

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
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.