Search code examples
jooq

How can I execute a Oracle Stored Procedure with jOOQ?

I get the name of a stored procedure as a configuration value and want to execute this procedure with some parameters using jOOQ.

Unfortunately I didn't find a method on the DSLContext like execute() or something.

How can I call a stored procedure using a string in jOOQ?

Solution

  • For more info on the generated stubs for stored procedures, see also this blog post.

    On the generated code of your stored procedure, you can use either one of two approaches. If your procedure is

    PROCEDURE p (i VARCHAR2, o OUT VARCHAR2);

    Call the static method

    For every procedure, there's a static method being generated in the Routines class, if it is a standalone procedure, or in a class named after your package if it is a package level class. You can call that procedure as follows:

    String o = Routines.p(configuration, i);

    The result type is:

    • void if there are no OUT parameters, and no return values
    • T if there is a single OUT parameter or return value of type T
    • <RoutineName> (a wrapper type) if there are more than one OUT parameter or return value

    Call the wrapper type

    The above static method is just a helper method to write the glue code around the routine wrapper type, which has been generated for each routine. You can also call this sequence of methods:

    P p = new P();
p.setI(i);
p.execute();
String o = p.getO();

    This corresponds to the contents of the static method. It is the preferred way of using the routine when there are overloads or default parameters.

    Not using the code generator

    jOOQ currently doesn't support calling stored procedures without using the code generator through public API. You could implement your own AbstractRoutine (just like generated code), but then why not just use code generation.

    You can obviously call a stored procedure using plain SQL, e.g.

    ctx.execute("DECLARE o VARCHAR2(10); BEGIN o := p(?); END;", i);

    But there is currently no way of declaring (or retrieving) an OUT parameter to plain SQL statements.

    The relevant feature request is https://github.com/jOOQ/jOOQ/issues/3426