Search

Top 60 Oracle Blogs

Recent comments

Arbitrary length addition and subtraction

This one just for fun today. An AskTOM question came in about arbitrary length arithmetic because “NUMBER(38) was not enough”. After some back-and-forth discussions it turned out that the business need under the requirement was managing bit strings. The implementation was currently converting the bits to decimals, hence the need for potentially very large number handling.

The problem was ultimately tackled with using RAW datatypes and holding the bits as raw strings, but I thought it would be interesting to throw together an addition and subtraction facility where the boundaries could exceed NUMBER(38).

So using nested tables, I had some fun with the code below.


SQL> set serverout on
SQL> declare
  2    type integer_array is  table of number;
  3    n1 integer_array :=
  4      integer_array(
  5        4,3,5,6,7,8,2,3,5,3,5,3,2,5,4,6,7,6,2,1,5,2,3,5,7,3,6,3,1,7,8,5,
  6        2,3,4,5,2,3,4,5,2,3,5,6,8,7,3,9,4,8,5,7,3,9,8,4,7,5,9,3,8,4,7,5,
  7        9,3,8,7,4,5,9,8,3,7,4,5
  8      );
  9    n2sign int := -1;
 10    n2 integer_array :=
 11      integer_array(
 12        0,0,0,0,0,0,3,4,5,2,3,4,5,2,4,3,7,6,8,5,6,7,5,6,7,6,7,8,6,7,8,5,
 13        4,5,6,3,4,5,7,4,5,6,7,4,5,6,7,5,8,5,6,7,8,5,6,7,9,8,9,3,8,4,7,5,
 14        3,4,6,4,5,4,6,5,7,7,4,5
 15      );
 16
 17    res integer_array := integer_array();
 18
 19    procedure add(a1 integer_array, a2 integer_array, r in out integer_array) is
 20      carry pls_integer := 0;
 21      tmp pls_integer;
 22    begin
 23      for i in reverse 1 .. a1.count
 24      loop
 25          tmp :=  a1(i)+a2(i)+carry;
 26          if tmp > 9 then
 27             carry := 1;
 28             tmp := tmp-10;
 29          else
 30             carry := 0;
 31          end if;
 32        r(i) := tmp;
 33      end loop;
 34    end;
 35
 36    procedure sub(s1 integer_array, s2 integer_array, r in out integer_array) is
 37      carry pls_integer := 0;
 38      tmp pls_integer;
 39    begin
 40      for i in reverse 1 .. s1.count
 41      loop
 42        tmp :=  S1(i)-S2(i)+carry;
 43        if tmp 



Definitely not complete implementations, but since addition and subtraction are things we learn in school, in the great tradition of school teachers around the world, I’ll close off this blog post with: “The rest of the implementation is left as an exercise” Smile