All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | Development versions: 3.12 | Unsupported versions: 3.2 | 3.1 | 3.0 | 2.6

Some databases support user-defined functions, which can be embedded in any SQL statement, if you're using jOOQ's code generator. Let's say you have the following simple function in Oracle SQL:

CREATE OR REPLACE FUNCTION echo (INPUT NUMBER)
RETURN NUMBER
IS
BEGIN
    RETURN INPUT;
END echo;

The above function will be made available from a generated Routines class. You can use it like any other column expression:

SELECT echo(1) FROM DUAL WHERE echo(2) = 2
create.select(echo(1)).where(echo(2).eq(2)).fetch();

Note that user-defined functions returning CURSOR or ARRAY data types can also be used wherever table expressions can be used, if they are unnested

The jOOQ Logo