Vendor Agnostic, Dynamic Procedural Logic with jOOQ

One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code.

SQL is a 4th generation programming language (4GL), and as such, extremely well suited for querying and bulk data manipulation. Its functional-declarative nature allows for it to be optimised in highly efficient ways using cost-based optimisation, but also statically as we’ve blogged before.

Sometimes, however, an imperative 3GL is better suited for a given task. That’s where stored procedures shine, or more specifically, procedural languages of RDBMS.

Among the ones that jOOQ supports, at least these ones support procedures:

  • BigQuery
  • Db2
  • Exasol
  • Firebird
  • HANA
  • HSQLDB
  • Informix
  • MariaDB
  • MySQL
  • Oracle
  • PostgreSQL
  • SQL Server
  • Vertica

Others may do, as well, but jOOQ isn’t supporting their dialects yet.

Many have implemented their own procedural languages, some according to the ISO/IEC 9075-4 Persistent stored modules (SQL/PSM) standard, others have their own.

jOOQ support for procedural logic

Since jOOQ 3.12, our commercial distributions have supported anonymous blocks and the procedural statements they contain, such as the IF statement, LOOP statements, etc. Starting with jOOQ 3.15, we also support 3 types of statements to manage storing procedural logic in the catalog:

Using these statements via jOOQ may not be your every day use-case. You may prefer managing that logic via the native syntax, which is still more powerful than what jOOQ 3.15 supports (especially when you’re using Oracle’s PL/SQL), in case of which you’ll u se jOOQ purely to call your procedure from Java in the usual type safe manner.

But maybe, you have one of these use-cases?

  • You’re a product vendor, and you profit from procedural logic being vendor agnostic in order to support multiple of your clients’ RDBMS
  • Your procedural logic is dynamic, just like your SQL logic (and what other than jOOQ to use for that?)
  • You don’t have the necessary privileges to create procedures, functions, or triggers in your schema

In all of those cases, jOOQ is here to help you.

How does it work?

The first building block is the anonymous block, which isn’t supported by all of the above dialects, regrettably. jOOQ can emulate it on MySQL as discussed here, but not currently in other dialects.

Here’s a simple, empty anonymous block:

-- Db2
BEGIN
END

-- Firebird
EXECUTE BLOCK AS
BEGIN
END

-- MariaDB
BEGIN NOT ATOMIC
END;

-- Oracle
BEGIN
  NULL;
END;

-- PostgreSQL
DO $$
BEGIN
  NULL;
END;
$$

It doesn’t really do much, but you can try executing it as follows, with jOOQ:

ctx.begin().execute();

Now, let’s do something more interesting, such as:

// Assuming the usual static imports:
import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType.*;

// Then write
Variable<Integer> i = variable(unquotedName("i"), INTEGER);
Table<?> t = table(unquotedName("t"));
Field<Integer> col = field(unquotedName("col"), INTEGER);

ctx.begin(
    declare(i).set(1),

    while_(i.le(10)).loop(
        insertInto(t).columns(c).values(i),
        i.set(i.plus(1))
    )
).execute();

The above block executes:

-- Db2
BEGIN
  DECLARE i integer;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END

-- FIREBIRD
EXECUTE BLOCK AS
  DECLARE i integer;
BEGIN
  :i = 1;
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (c)
    VALUES (:i);
    :i = (:i + 1);
  END
END

-- MariaDB
BEGIN NOT ATOMIC
  DECLARE i int;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END;

-- Oracle
DECLARE
  i number(10);
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;

-- PostgreSQL
DO $$
DECLARE
  i int;
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;
$$

-- SQL Server
BEGIN
  DECLARE @i int;
  SET @i = 1;
  WHILE @i <= 10 BEGIN
    INSERT INTO t (c)
    VALUES (@i);
    SET @i = (@i + 1);
  END;
END;

Easy as pie. Perhaps you prefer a FOR loop, instead? Try this:

ctx.begin(
    for_(i).in(1, 10).loop(
        insertInto(t).columns(c).values(i)
    )
).execute();

It produces the necessary emulations, if required, because regrettably, not all dialects support FOR:

-- Db2
BEGIN
  DECLARE i integer;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END

-- Firebird
EXECUTE BLOCK AS
  DECLARE i integer;
BEGIN
  :i = 1;
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (c)
    VALUES (:i);
    :i = (:i + 1);
  END
END

-- MariaDB
BEGIN NOT ATOMIC
  FOR i IN 1 .. 10 DO
    INSERT INTO t (c)
    VALUES (i);
  END FOR;
END;

-- Oracle
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
  END LOOP;
END;

-- PostgreSQL
DO $$
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
  END LOOP;
END;
$$

-- SQL Server
BEGIN
  DECLARE @i int;
  BEGIN
    SET @i = 1;
    WHILE @i <= 10 BEGIN
      INSERT INTO t (c)
      VALUES (@i);
      SET @i = (@i + 1);
    END;
  END;
END;

SQL vs procedures

Of course, this particular SQL statement would be better implemented using a single bulk insertion statement, purely with SQL, not with procedural logic

ctx.insertInto(t, c)
   .select(selectFrom(generateSeries(1, 10)))
   .execute();

Which translates to:

-- Db2
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  WITH
    generate_series(generate_series) AS (
      SELECT 1
      FROM SYSIBM.DUAL
      UNION ALL
      SELECT (generate_series + 1)
      FROM generate_series
      WHERE generate_series < 10
    )
  SELECT generate_series
  FROM generate_series
) generate_series;

-- Firebird
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  WITH RECURSIVE
    generate_series(generate_series) AS (
      SELECT 1
      FROM RDB$DATABASE
      UNION ALL
      SELECT (generate_series + 1)
      FROM generate_series
      WHERE generate_series < 10
    )
  SELECT generate_series
  FROM generate_series
) generate_series;

-- MariaDB
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  WITH RECURSIVE
    generate_series(generate_series) AS (
      SELECT 1
      UNION ALL
      SELECT (generate_series + 1)
      FROM generate_series
      WHERE generate_series < 10
    )
  SELECT generate_series
  FROM generate_series
) AS generate_series;

-- Oracle
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  SELECT (level + (1 - 1)) generate_series
  FROM DUAL
  CONNECT BY level <= ((10 + 1) - 1)
) generate_series;

-- PostgreSQL
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM generate_series(1, 10);

-- SQL Server
WITH
  generate_series(generate_series) AS (
    SELECT 1
    UNION ALL
    SELECT (generate_series + 1)
    FROM generate_series
    WHERE generate_series < 10
  )
INSERT INTO t (c)
SELECT generate_series.generate_series
FROM (
  SELECT generate_series
  FROM generate_series
) generate_series

… but you get the point.

Storing the procedural logic

If you have the necessary privileges, and your procedural logic isn’t super dynamic, you may choose to store your logic in a procedure or function directly in your database. In some databases, this means a compiler will be able to eagerly translate the logic to something very efficient (e.g. machine code), instead of interpreting the logic on the fly.

Take the above WHILE loop, for example. You may want to store that as a procedure P:

Name p = unquotedName("p");

ctx.createProcedure(p)
   .modifiesSQLData()
   .as(
        declare(i).set(1),

        while_(i.le(10)).loop(
            insertInto(t).columns(c).values(i),
            i.set(i.plus(1))
        )
   )
   .execute();

This produces the following statements:

-- Db2
CREATE PROCEDURE p()
MODIFIES SQL DATA
BEGIN
  DECLARE i integer;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END;

-- Firebird
CREATE PROCEDURE p()
AS
  DECLARE i integer;
BEGIN
  :i = 1;
  WHILE (:i <= 10) DO BEGIN
    INSERT INTO t (c)
    VALUES (:i);
    :i = (:i + 1);
  END
END

-- MariaDB
CREATE PROCEDURE p()
MODIFIES SQL DATA
BEGIN
  DECLARE i int;
  SET i = 1;
  WHILE i <= 10 DO
    INSERT INTO t (c)
    VALUES (i);
    SET i = (i + 1);
  END WHILE;
END;

-- Oracle
CREATE PROCEDURE p
AS
  i number(10);
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;

-- PostgreSQL
CREATE PROCEDURE p()
LANGUAGE plpgsql
AS
$$
DECLARE
  i int;
BEGIN
  i := 1;
  WHILE i <= 10 LOOP
    INSERT INTO t (c)
    VALUES (i);
    i := (i + 1);
  END LOOP;
END;
$$

-- SQL Server
CREATE PROCEDURE p
AS
BEGIN
  DECLARE @i int;
  SET @i = 1;
  WHILE @i <= 10 BEGIN
    INSERT INTO t (c)
    VALUES (@i);
    SET @i = (@i + 1);
  END;
END;

And now, what better way to call this procedure than, again, an anonymous block?

ctx.begin(call(unquotedName("p"))).execute();

Producing:

-- Db2
BEGIN
  CALL p();
END

-- Firebird
EXECUTE BLOCK AS
BEGIN
  EXECUTE PROCEDURE p;
END

-- MariaDB
BEGIN NOT ATOMIC
  CALL p();
END;

-- Oracle
BEGIN
  p();
END;

-- PostgreSQL
DO $$
BEGIN
  CALL p();
END;
$$

-- SQL Server
BEGIN
  EXEC p ;
END;

If you’re using jOOQ in Flyway or Liquibase to generate procedures during your database migrations, you can obviously generate jOOQ procedure stubs to call in a more type safe manner, instead of the above dynamic procedure call.

Parsing procedural logic

This jOOQ feature is not really exceptional. You can play around with our parser / translator here: https://www.jooq.org/translate. It can definitely help you translate your (simpler) stored procedures between dialects, such as PL/SQL, T-SQL, PL/pgSQL, etc.

Conclusion

As a rule of thumb, if you can do it with SQL (the 4GL), do it with SQL alone. But sometimes, you can’t. A 3GL is a better choice for an algorithm. When using jOOQ, you’ll naturally think of using Java to implement that 3GL algorithm. But wait, you could move the logic to the server for (drastically) increased performance!

Thanks to jOOQ, you can generate procedural logic that is:

  • Dynamic
  • Vendor agnostic
  • Anonymous or stored

Just like you’re used to, from jOOQ, for SQL

jOOQ as a “PL/Java” language

Some people who get in touch with PL/SQL, PL/pgSQL, T-SQL, or any other proprietary procedural language for SQL interaction are probably missing out on a couple of language integration features in the Java world. Most Java APIs see SQL as an external domain-specific language that is “best” dealt with using string concatenation. Such APIs include:

Other APIs aim to abstract SQL away, in favour of a “higher-level” mapping to objects. These, again, include

As can be seen quickly, a lot of tool vendors and developers have travelled down similar ORM roads to try to tackle the “mapping problem” from a slightly (never fundamentally) different approach.

But not all people want ORM. Many people want SQL. A nice, general opinion about the old ORM vs. SQL discussion was phrased by Ken Downs a while ago:
http://database-programmer.blogspot.ch/2010/12/historical-perspective-of-orm-and.html

SQL as an internal domain-specific language

We can all agree that SQL itself is a domain-specific language, a language specific to the domain of database querying and database manipulation. As mentioned before, SQL is enhanced on some platforms by proprietary, procedural extensions, some of which even made it into the SQL standard (although barely implemented in the standard form, apart from HSQLDB).

The main advantage of such procedural SQL language extensions is the fact that imperative control flow can be combined with declarative SQL statement execution. Both language paradigms have their place. One is ideal to model control flows, the other is ideal to model queries, abstracting boring querying algorithms.

But imperative programming is quite limited itself. It is difficult to profit from advantages offered by object-oriented or functional paradigms, implemented by popular languages like Java or Scala. Those who have tried Oracle PL/SQL’s “object-oriented” extensions may know what I mean. Furthermore, each procedural extension is vendor-specific and has its own learning curve.

jOOQ models SQL as an internal domain-specific language in Java, and can thus be seen as enhancing Java with some procedural aspects. This has been shown previously on this blog, through an example using H2 database triggers, written in Java/jOOQ. What was meant to be a proof of concept and a nice idea was now re-created by Ronny Guillaume, who wrote an interesting article (in French) about using jOOQ as PL/Java within a Postgres database! The article can be seen here:

http://ronnyguillaume.developpez.com/introduction-pl-java

In essence, you can use another third-party tool called pljava, compile and wrap jOOQ code into a jar file and deploy that jar file into your Postgres database before using it in regular Postgres SQL, or as a trigger. Similar things can be done in Java databases, such as Derby, H2, and HSQLDB, and even in the Oracle database (for the brave among you).

Looking forward to finding more interesting articles about using jOOQ for PL/Java in the wild!

Use jOOQ inside your H2 database

I recently became aware of an interesting use-case for jOOQ when I was optimising my own H2 database integration tests:

H2 stored functions

H2 knows two operation modes for stored functions:
  1. “Inline mode” with source code provided
  2. “Reference mode” referencing a public static method of a Java class on the databases’ classpath
The above terms are not official H2-speak. For more information, consider the documentation: http://www.h2database.com/html/grammar.html#create_alias
http://www.h2database.com/html/features.html#user_defined_functions Now, regardless of the operation mode, your H2 stored functions are always written in Java. There is currently no PL/SQL-like procedural language. When your function needs to access the database again for further data processing, you’re back to JDBC, and that’s a pity. That makes writing stored functions quite verbose again.

Using jOOQ within H2 stored functions

… so why not just use jOOQ within the H2 database? Here’s a simple example about how you could do that: Write your stored function When writing your own stored function, you can use source code previously generated by jOOQ. This means that the SQL within your stored functions (triggers, etc) will actually compile!

package org.jooq.test.h2;

import static test.generated.Tables.*;

import java.sql.Connection;
import java.sql.SQLException;

import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import test.generated.tables.TBook;

public class Functions {
  /**
   * This function returns the number of
   * books written by a given author.
   */
  public static int countBooks(
    Connection connection, 
    Integer authorId) 
  throws SQLException {
    return DSL.using(connection, SQLDialect.H2)
              .selectCount()
              .from(T_BOOK)
              .where(T_BOOK.AUTHOR_ID.eq(authorId))
              .fetchOne(0, int.class);
  }
}

Declare the above method as an ALIAS to H2

CREATE ALIAS countBooks 
   FOR "org.jooq.test.h2.Functions.countBooks";

Use the function in SQL

select t_author.last_name, countBooks(id) 
from t_author

… or with jOOQ’s generated classes jOOQ generates a Routines class containing static access to all of your stored functions. This means that also your client code can make compile-safe use of your stored functions.

package org.jooq.test.h2;

import static test.generated.Tables.*;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.jooq.SQLDialect;
import org.jooq.impl.DSL;
import test.generated.Routines;

public class Test {
  public static void main(String[] args) 
  throws SQLException {
    Connection connection = 
      DriverManager.getConnection(
        "jdbc:h2:~/test", "sa", "");

    System.out.println(
    DLS.using(connection, SQLDialect.H2)
       .select(
             T_AUTHOR.LAST_NAME,
             Routines.countbooks(T_AUTHOR.ID))
       .from(T_AUTHOR)
       .fetch());
  }
}

The logged output of the above will be
+---------+---------------------+
|LAST_NAME|"PUBLIC"."COUNTBOOKS"|
+---------+---------------------+
|Orwell   |                    2|
|Coelho   |                    2|
|Hesse    |                    0|
+---------+---------------------+

Conclusion

jOOQ can also be used within your database, wherever Java is supported. In such a case, jOOQ acts like a PL/SQL-like extension to your favourite database’s Java stored procedure / stored function support. Give it a try, yourself!

What are procedures and functions after all?

Many RDBMS support the concept of “routines”, usually calling them procedures and/or functions. These concepts have been around in programming languages for a while, also outside of databases. Famous languages distinguishing procedures from functions are:

  • Ada
  • BASIC
  • Pascal
  • etc…

The general distinction between (stored) procedures and (stored) functions can be summarised like this:

Procedures:

  • Are called using JDBC CallableStatement
  • Have no return value
  • Usually support OUT parameters

Functions:

  • Can be used in SQL statements
  • Have a return value
  • Usually don’t support OUT parameters

But there are exceptions to these rules:

  • DB2, H2, and HSQLDB don’t allow for JDBC escape syntax when calling functions. Functions must be used in a SELECT statement
  • H2 only knows functions (without OUT parameters)
  • Oracle functions may have OUT parameters
  • Oracle knows functions that mustn’t be used in SQL statements for transactional reasons
  • Postgres only knows functions (with all features combined). OUT parameters can also be interpreted as return values, which is quite elegant/freaky, depending on your taste
  • The Sybase jconn3 JDBC driver doesn’t handle null values correctly when using the JDBC escape syntax on functions

In general, it can be said that the field of routines (procedures / functions) is far from being standardised in modern RDBMS. Every database has its ways and JDBC only provides little abstraction over the great variety of procedures / functions implementations, especially when advanced data types such as cursors / UDT’s / arrays are involved.

Oracle scalar subquery caching

The importance of being able to fully control executed SQL (using jOOQ, or plain JDBC) on large-scale systems becomes obvious every time you need to fine-tune your SQL queries for a specific RDBMS. In this case, we’re looking at Oracle and its miraculous scalar subquery caching mechanisms: Usually, the context switch from SQL to PL/SQL and back is quite expensive for an average Oracle query, so we should normally omit putting stored functions as filtering, grouping, sorting criteria, even if they’re DETERMINISTIC. When we have large result sets, however, even functions in the query projection may turn out to be an execution plan nightmare. I recently raised the question on Stack Overflow, as I’m curious if there is any caching mechanism, that I could take advantage of for a concrete use-case: http://stackoverflow.com/questions/7270467/is-there-a-pl-sql-pragma-similar-to-deterministic-but-for-the-scope-of-one-singl And most interestingly, there are several. The accepted answer points to this very relevant article on Ask Tom’s Q/A: http://www.oracle.com/technetwork/issue-archive/2011/11-sep/o51asktom-453438.html Apparently, in scalar subqueries, function results may be cached! While this can be very useful for performance, it can also be very dangerous for consistency – as with any caching mechanism, but this one is highly implicit! So this will be cached:

-- my_function is NOT deterministic but it is cached!
select t.x, t.y, (select my_function(t.x) from dual)
from t

-- logically equivalent to this, uncached
select t.x, t.y, my_function(t.x) from t

Knowing this, I think I have to review quite a bit of SQL…

Oracle’s object-oriented PL/SQL extensions

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!

The power of REF CURSOR types

Many RDBMS have started implementing support for some sort of CURSOR, REF CURSOR or ARRAY/TABLE types. These types have roughly the same semantics as JDBC‘s  java.sql.Array and java.sql.ResultSet. In principle, such types can appear anywhere in SQL, even if some RDBMS limit support to a certain functionality subset. What exactly are these types?

ARRAY types

ARRAY types are the easiest to understand. An array is usually implemented as a typed collection of values. They can be used both for table columns and with stored procedures. Most database schema designers would agree that using ARRAY types in tables is not necessarily a good idea, as this will lead to a schema that is normalised in a “non-first normal form“. On the other hand, ARRAY types can be very powerful when used as stored procedure parameters and especially as stored function results. In both scenarios (table columns, function results), arrays can be unnested using operators such as TABLE(…) or UNNEST(…). These operators will make the contents of an array available to any SQL clause that takes tables as arguments. An example (in HSQLDB):

-- unnest an ad-hoc anonymously typed array
SELECT element
FROM unnest(array[1, 2, 3]) AS unnested(element);

A similar example (in Oracle, which doesn’t support anonymous ARRAY types):

-- create a typed array of numbers
CREATE TYPE number_array AS VARRAY(10) OF NUMBER(7);

-- unnest an ad-hoc array instance. Oracle will name
-- the column of such an unnested array "COLUMN_VALUE"
SELECT column_value
FROM table(number_array(1, 2, 3));

Both constructs will result in a simple table holding one column and three records: 1, 2, 3. As mentioned previously, the power of such ARRAY types becomes obvious, when using them as results in stored functions. In Oracle, for instance, you could define a function like this:

-- a simple example function
CREATE FUNCTION get_array RETURN number_array IS
BEGIN
  RETURN number_array(1, 2, 3);
END;

-- unnest results from a function returning an array
SELECT column_value
FROM TABLE(get_array);

With these syntactical outlines, you can define very complex functions returning well-defined types, which can then again be unnested into SQL tables. The power of this functionality knows little limits, when you combine ARRAY types with OBJECT types, in those RDBMS that support it. Again, with Oracle you can write:

-- A simple, reusable person type
CREATE TYPE person AS OBJECT(
  id NUMBER(7),
  name VARCHAR2(100)
);

-- An array of such users
CREATE TYPE person_array AS VARRAY(10) OF person;

-- An unnested array of such users used in SQL:
SELECT *
FROM TABLE(person_array(
  person(1, 'Jim'),
  person(2, 'Joe')
));

The above SELECT statement will intuitively result in a two-column, two-record table looking like this:
ID NAME
1 Jim
2 Joe
Again, this could be used in combination with stored functions that do a lot more complex processing first to calculate the above result including Jim and Joe.

TABLE types

Some RDBMS (e.g. Oracle) distinguish between in-memory ARRAY types (e.g. the VARRAY type we’ve seen before), and in-memory TABLE types. The main difference for this article is the fact that VARRAY types have a maximum size, whereas TABLE types can be extended to any arbitrary length. Also, Oracle’s API for manipulating nested tables directly in SQL is richer than that of VARRAY types, if you want to add a record to a table nested in another table, for instance. A thorough comparison would be out of scope here, though.

REF CURSORS

Cursors, and in particular REF CURSORS, are handled differently in a way that they don’t directly contain all of their data, but can be iterated (or “looped”) over. Also, a REF CURSOR is a weakly typed object, which means that the number and types of a REF CURSOR’s columns cannot be known at SQL compile-time (or “parse-time”), but only when the SQL statement is executed. This makes it harder to use REF CURSOR directly in SQL. Oracle’s TABLE(…) function, for instance, does not support REF CURSOR types as parameter. See also an overview of what’s possible and what isn’t on this Stack Overflow question: http://stackoverflow.com/questions/6410452/fetch-oracle-table-type-from-stored-procedure-using-jdbc Nevertheless, a REF CURSOR can be returned from a stored procedure or from a stored function, and retrieved in JDBC like any other java.sql.ResultSet.

jOOQ’s support for ARRAY/CURSOR types

One of jOOQ’s major goals is to allow its users to easily integrate advanced RDBMS concepts into Java, a non-trivial endeavour when using JDBC. These concepts have not yet (to my knowledge) been standardised in SQL:2008. Every RDBMS that supports those concepts, employs its own syntax. The most curious (yet also the most powerful) syntax I have encountered is that of the H2 database, where unnesting of arrays can be done like this:

-- unnest an ad-hoc anonymously typed array
SELECT *
FROM TABLE(
  ID INT=(1, 2),
  NAME VARCHAR=('Hello', 'World'));

The above example can be found here: http://www.h2database.com/html/functions.html#table Apart from hiding these many SQL syntax facts from the user, jOOQ also aims at hiding JDBC statement preparing and type mapping from the user. Passing arrays to a prepared statement is not trivial with Oracle, fetching ResultSets from stored functions isn’t either. And none of todays major frameworks, including Hibernate/JPA, Spring, myBatis, etc, support an easy way to integrate such data types and stored procedures automatically into Java – although Spring allows you to write one custom mapper per procedure. With jOOQ this is going to be possible. While ARRAY types have been supported by jOOQ for quite some time now, unnesting them is part of the currently running “project CURSOR”. The API is being extended to handle the examples given in SQL, above. An example of such API usage is this:

// Create the usual jOOQ factory
Factory create = new OracleFactory(connection);

// Loop over the values returned from thegenerated getArray function
for (Record record : create.select()
                           .from(create.table(getArray()))
                           .fetch()) {

    // This will print 1, 2, 3
    System.out.println(record.getValue(0));
}

Advanced data types as a future investment

The power of these data types have long been known (and loved) by DBA and database programmers using PL/SQL and other database languages. They have been avoided by Java developers mainly because of the awkwardness (or even lack) of JDBC and/or JPA support. It is difficult for an unexperienced JDBC developer, to correctly bind arrays of object to an Oracle prepared statement, or to fetch a REF CURSOR from a CallableStatement. The depicted functionality is already available with jOOQ 1.6.3. Many other features dealing with CURSOR and ARRAY types will be implemented in the near future. …so start using the full functionality set of your database!