Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for PL/SQL BOOLEAN types #4155

Closed
lukaseder opened this issue Mar 25, 2015 · 0 comments

Comments

Projects
None yet
1 participant
@lukaseder
Copy link
Member

commented Mar 25, 2015

Unlike PL/SQL RECORD and TABLE types (see #330), PL/SQL BOOLEAN types are rather easy to emulate in stored procedure calls by jOOQ. Consider:

CREATE PACKAGE pls_objects AS
  PROCEDURE p_bool (i IN BOOLEAN, io IN OUT BOOLEAN, o OUT BOOLEAN);
END pls_objects;
/

The above procedure can be called like this:

Connection c = getConnection();
CallableStatement call = c.prepareCall(
    " DECLARE"
  + "   vi1 BOOLEAN := CASE ? WHEN 1 THEN TRUE WHEN 0 THEN FALSE ELSE NULL END;"
  + "   vi2 BOOLEAN := CASE ? WHEN 1 THEN TRUE WHEN 0 THEN FALSE ELSE NULL END;"
  + "   vi3 BOOLEAN;"
  + " BEGIN"
  + "   pls_objects.p_bool(vi1, vi2, vi3);"
  + ""
  + "   ? := CASE vi2 WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE NULL END;"
  + "   ? := CASE vi3 WHEN TRUE THEN 1 WHEN FALSE THEN 0 ELSE NULL END;"
  + " END;");

call.setObject(1, 1);
call.setObject(2, null);
call.registerOutParameter(3, Types.INTEGER);
call.registerOutParameter(4, Types.INTEGER);
call.execute();

System.out.println(call.getBoolean(3) + " (was null: " + call.wasNull() + ")");
System.out.println(call.getBoolean(4) + " (was null: " + call.wasNull() + ")");

The idea that whenever a PL/SQL type is encountered, it is first assembled in a DECLARE section from its "parts", i.e. from SQL/JDBC-compatible types like INTEGER in the above example. Then, the local variables are passed to the procedure / function, and the results are stored again in local variables, from which OUT parameters can be derived.


Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.