 # Arbitrary length addition and subtraction Posted by

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      );
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” 1. JAYT22 says:

With 5/10 for spelling of the mantra… 😉

1. Connor McDonald says:

LOL! Thanks, will correct

2. Stelios Vlasopoulos says:

Just for fun here is the karatsuba multiplication in pl/sql :
CREATE OR REPLACE FUNCTION karatsuba (x IN NUMBER, y IN NUMBER)
RETURN NUMBER
IS
deg NUMBER;
x1 NUMBER;
x2 NUMBER;
y1 NUMBER;
y2 NUMBER;
z0 NUMBER;
z1 NUMBER;
z2 NUMBER;
len_x INT;
len_y INT;
BEGIN
IF (x < 10) or (y < 10) THEN
RETURN x*y;
END IF;
/* calculates the size of the numbers */
len_x := LENGTH (x);
len_y := LENGTH (y);
/* split the digit sequences about the middle */
x1 := TO_NUMBER (SUBSTR ( TO_CHAR(x), 1, CEIL (len_x/2)));
x2 := TO_NUMBER (SUBSTR ( TO_CHAR(x), CEIL (len_x/2)+1));
y1 := TO_NUMBER (SUBSTR ( TO_CHAR(y), 1, CEIL (len_y/2)));
y2 := TO_NUMBER (SUBSTR ( TO_CHAR(y), CEIL (len_y/2)+1));
–dbms_output.put_line(x1||','||x2||','||y1||','||y2);
deg := FLOOR (len_x/2);
/* 3 calls made to numbers approximately half the size */
z0 := karatsuba (x1, y1);
–dbms_output.put_line('z0='||z0);
z2 := karatsuba (x2, y2);
–dbms_output.put_line('z2='||z2);
z1 := karatsuba (x1+x2, y1+y2);
–dbms_output.put_line('z1='||z1);
RETURN z0 * POWER(10, (2*deg))
+ z2
+ (z1 – z0 – z2) * POWER(10, deg);
END;
/

1. Anton Scheffer says:

Nice example of how to tackle a “large” problem. You can store more than one decimal in your integer_array by the way. Makes it a lot faster (I use a simular technique to do some public/private key calculations)

1. Connor McDonald says:

Ah of course!

Thanks for stopping by. I’m a big fan of your PLSQL and SQL contributions to the community

1. Anton Scheffer says:

See https://github.com/antonscheffer/as_sftp for my latest contribution, it uses those large additions (and multiplications).

This site uses Akismet to reduce spam. Learn how your comment data is processed.