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:
http://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!
Hi. Was this feature implemented in JOOQ? Thanks!
Yes, thanks for the hint. I’ll edit the blog post. It was added long ago: https://github.com/jOOQ/jOOQ/issues/799