René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

August 19, 2005: On emulating callback functions through deriving an object from another in PL/SQL

PL/SQL does not have function pointers like for example C. In C, function pointers are used to (sort of) pass an arbitrary function to an algorithm of any kind. The algorithm can then call the passed function without knowing what this function does.
Such an algorithm is for example Newton's Method. This algorithm is (sometimes) able to approximate the solution for F(x) = y if the function's first derivative F'(x) (or dF(x)) is known. So, in C, one passes a function pointer to F and another function pointer to dF (as well as y) to the algorithm in order to solve the equation.
In this article, I hope I can show how to emulate function pointers in PL/SQL through deriving an object.
First, a base object (or class, in OO lingo) is created: 
create type newton_method as object (

  e        number,
  max_iter number,

  -- Derive from this class and override the following two methods:
  not instantiable member function  F(x in number) return number,
  not instantiable member function dF(x in number) return number,

  final member function solve(y in number, initial_guess in number := 1) return  number

) not instantiable not final;
/
As the newton method approximates the solution, the desired accuracy of the solution must be given. This accuracy is stored in the member e. The closer e is to 0 (zero), the more accurate is the solution.
Also, the newton method is an iterative approach. In order to make sure that the iteration does not loop endlessly or very long, the maximum number of iterations allowed is stored in the member max_iter.
The algorithm is invoked by the method solve which takes two parameters: y (being the y of F(x)=y) and initial_guess.
The two methods F and dF must be overridden in the derived class.
Then, there is also a type body: 
create type body newton_method as

  final member function solve(y in number, initial_guess in number := 1) return  number is
    current_guess number := initial_guess;
    dFvalue       number;
     Fvalue       number;
  begin

    for i in 1 .. max_iter loop
      Fvalue := F(current_guess) - y;
      exit when abs(Fvalue) < e;

      dFvalue := dF(current_guess);

      -- No divisions by zero!
      if dFvalue = 0 then return null; end if;

      current_guess := current_guess - Fvalue / dFvalue;
    end loop;

    return current_guess;

  end solve;

end;
/
The important detail to observe here is that solve calls both F() and dF(), yet, what F and dF do is completely unknown.
We're now ready to create a derived class in order to solve F(x) = x*x = y. First, the type definition. 
create type newton_quadratic under newton_method (

  overriding member function  F(x in number) return number,
  overriding member function dF(x in number) return number

);
/
The body then implements F() and its derivation: dF(), which is 2*x: 
create type body newton_quadratic as

  overriding member function  F(x in number) return number is begin
    return x*x;
  end;

  overriding member function dF(x in number) return number is begin
    return 2*x;
  end;

end;
/
Let's see this class in action: 
set serveroutput on size 10000

declare
  q newton_quadratic:= newton_quadratic(e => 0.00001, max_iter => 100);
begin

  for i in 1 .. 9 loop
    dbms_output.put_line('square root of ' || i || ': ' || q.solve(i));
  end loop;

end;
/

square root of 1: 1
square root of 2: 1.41421568627450980392156862745098039216
square root of 3: 1.73205081001472754050073637702503681885
square root of 4: 2.0000000929222946603132596397588480609
square root of 5: 2.23606889564336372847011144883485309017
square root of 6: 2.44948974278755165583832768918086380818
square root of 7: 2.64575131111136932047478806041526127088
square root of 8: 2.82842712504986412995671609183567406561
square root of 9: 3.00000000139698386224847842525888197712
Obviously 9=3*3, not 3.00000000139698386224847842525888197712*3.00000000139698386224847842525888197712. This result is because it's an approximation.

More on Oracle

This is an on Oracle article. The most current articles of this series can be found here.