How do I pass Oracle PL/SQL constants as parameters using jOOQ?

Issue

I'm trying to convert some Oracle queries using PL/SQL to jOOQ. Most package queries, stored procedures, etc. are easy, using the code generator. However there's one feature used in several places for which I haven't found a jOOQ alternative:

begin
  MY_SCHEMA.MY_PACKAGE.MY_QUERY(some_param => MY_SCHEMA.MY_PACKAGE.SOME_CONSTANT)
  -- more code
end;

I can call the query just fine, but I'm not sure how to pass the MY_SCHEMA.MY_PACKAGE.SOME_CONSTANT value into it. The jOOQ code generator doesn't seem to generate anything for the constant (at least, I can't find anything similarly named). Do I need to enable a feature on the generator? Or do I need to query those constants? If so, how?

Solution

Enabling PL/Scope for this to work

jOOQ can generate code for your package constants if it can find them in your ALL_IDENTIFIERS dictionary view. That's only the case if you enable PLSCOPE_SETTINGS when compiling your packages, e.g. using:

ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL'

With that in place, jOOQ will generate expressions for your package constants, which you can use in routine calls, or other procedural logic.

PL/Scope independence

What Simon Martinelli referred to in the comments is issue https://github.com/jOOQ/jOOQ/issues/6504, which attempts to enable this code generation support even without the above PL/Scope setting turned on, because that's quite unreliable depending on your environment.

As of jOOQ 3.15, there's no solution yet that works on any Oracle environment. But you could use testcontainers to generate your jOOQ code from a Docker image that has PL/Scope enabled.

Answered By - Lukas Eder