René Nyffenegger's collection of things on the web  
René Nyffenegger on Oracle  Most wanted  Feedback
 Follow @renenyffenegger

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 OracleThis is an on Oracle article. The most current articles of this series can be found here.
