Calling Procedures with Default Parameters using JDBC or jOOQ

Using jOOQ’s code generator to call stored procedures is a popular reason to use jOOQ. For example, when you have a procedure like the following Oracle PL/SQL procedure:

CREATE OR REPLACE PROCEDURE p (
  p_i1 IN number,
  p_o1 OUT number,
  p_i2 IN varchar2,
  p_o2 OUT varchar2
)
IS
BEGIN
  p_o1 := p_i1;
  p_o2 := p_i2;
END;

jOOQ will generate code for you to call very simply, like this:

// Configuration contains your JDBC Connection, and other things
P result = Routines.p(configuration, 1, "A");
System.out.println(p.getPO1());
System.out.println(p.getPO2());

This will execute the following, taking care of binding all IN and OUT parameters for you:

{ call "TEST"."P" (?, ?, ?, ?) }

The output of the program is:

1
A

Now, what if you’re adding DEFAULT values to your procedure (or function) signature?

CREATE OR REPLACE PROCEDURE p (
  p_i1 IN number := 1,
  p_o1 OUT number,
  p_i2 IN varchar2 := 'A',
  p_o2 OUT varchar2
)
IS
BEGIN
  p_o1 := p_i1;
  p_o2 := p_i2;
END;

In your Java code above, there’s no way to omit the parameter of the Routines.p() call, but if you look at the generate implementation of Routines.p(), you can see that this is just convenience for using positional parameter indexes (as we’re used to do in Java). You can always instantiate the procedure call directly, like this – there’s no technical difference between the two ways to call the procedure:

P p = new P();
p.setPI1(2);
p.setPI2("B");
p.execute(configuration);
System.out.println(p.getPO1());
System.out.println(p.getPO2());

With the above syntax, you can omit any parameter that you know is defaulted, e.g.:

P p = new P();
p.setPI1(2);
p.execute(configuration);
System.out.println(p.getPO1());
System.out.println(p.getPO2());

Now, instead of the JDBC escape syntax, jOOQ will render an anonymous block like this:

begin
  "TEST"."P" ("P_I1" => ?, "P_O1" => ?, "P_O2" => ?)
end;

Note how P_I2 isn’t being passed explicitly to the procedure call.

The output is:

2
A

This works on any RDBMS that supports default parameters, each with their own specific syntax to pass parameters by name, including at least:

• Db2
• Informix
• Oracle
• PostgreSQL
• SQL Server