Oracle’s object-oriented PL/SQL extensions

Posted on by lukaseder
I have recently re-discovered an interesting feature of Oracle‘s PL/SQL language. Not only can you define your own types very easily, you can also associate “methods” to them, as in other object-oriented languages. Oracle calls those “methods” member functions and member procedures. This is documented here, for example: https://download.oracle.com/docs/cd/B28359_01/appdev.111/b28371/adobjbas.htm#i477669 So you can define your own type like this 

create type circle as object (
   radius number,
   member procedure draw,
   member function  circumference return number
);

create type body circle as
   member procedure draw is
   begin
     null; -- draw the circle
   end draw;

   member function circumference return number is
   begin
     return 2 * 3.141 * radius;
   end circumference;
end;
In PL/SQL, you can instanciate that type and call its member procedures and functions easily: 

declare
   c circle;
begin
   c := circle(5);
   dbms_output.put_line(to_char(c.circumference));
end;
The same function can be called in JDBC with this statement 

CallableStatement call = c.prepareCall(
  "{ ? = call circle(5).circumference() }");
It would only feel natural for jOOQ-generated UDT records to provide access to the underlying member procedures and functions. The jOOQ UDTRecord is an attachable object. That means, that if it is fetched from the database, it holds a reference to a jOOQ Configuration, and thus to a JDBC Connection. So if you create a procedure that returns a circle, you can call procedures and functions directly on that circle. Your Java code might look like this: 

// Call the stored function that returns a new circle type
CircleRecord circle = Functions.getNewCircle(configuration);

// Use the attached CircleRecord to calculate the circumference
BigDecimal circumference = circle.circumference();

// And draw the circle in the database
circle.draw();
Look out in future versions of jOOQ for this exciting new feature! This awesome feature has been part of jOOQ for a long time now!

Published by lukaseder

I made jOOQ

2 thoughts on “Oracle’s object-oriented PL/SQL extensions”

Leave a Reply