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.

Sign up for GitHub

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

Jump to bottom

PL/SQL procedure returns wrong result when combining boolean and record type as input #14839

Closed
jthewes opened this issue Mar 22, 2023 · 4 comments
Closed

PL/SQL procedure returns wrong result when combining boolean and record type as input #14839

jthewes opened this issue Mar 22, 2023 · 4 comments
Labels
C: DB: Oracle C: Functionality E: Enterprise Edition E: Professional Edition P: High R: Fixed T: Defect
Projects
3.19 Other improvements
Milestone
Version 3.19.0

Comments

@jthewes
Copy link

jthewes commented Mar 22, 2023

Expected behavior

When calling a PL/SQL procedure with a record type input parameter and a boolean input parameter, the procedure returns an correct result and the input parameters are in correct order.

Actual behavior

When calling a PL/SQL procedure with a record type input parameter and a boolean input parameter, the procedure returns an incorrect result. Apparently, the filling of the input parameter in this constellation is incorrectly. This error seems to occur only in combination of record type and boolean. The combination of primitive types and Boolean behaves as expected.

Steps to reproduce the problem

  • Define a PL/SQL procedure with a record type input parameter, a boolean input parameter and a varchar output parameter on an Oracle database
-- boolean_test.pck
CREATE OR REPLACE PACKAGE dev.boolean_test AS

  TYPE t_rec_address IS RECORD(
    zip      VARCHAR2(5),
    city     VARCHAR2(100),
    street   VARCHAR2(100),
    house_no VARCHAR2(20));

  PROCEDURE boolean_with_type(p_address IN dev.boolean_test.t_rec_address, p_boolean IN BOOLEAN, p_output OUT VARCHAR2);

END boolean_test;
-- boolean_test.pkb
CREATE OR REPLACE PACKAGE BODY dev.boolean_test AS

  PROCEDURE boolean_with_type(p_address IN dev.boolean_test.t_rec_address, p_boolean IN BOOLEAN, p_output OUT VARCHAR2) IS
  BEGIN
    logger.info('ZIP: {}', ot_string(p_address.zip));
    logger.info('City: {}', ot_string(p_address.city));
    logger.info('Street: {}', ot_string(p_address.street));
    logger.info('House_no: {}', ot_string(p_address.house_no));
    logger.info('Boolean: {}', ot_string(p_boolean));
  
    IF p_boolean THEN
      p_output := 'TRUE';
    ELSE
      p_output := 'FALSE';
    END IF;
  END;

END boolean_test;
  • Generate using jOOQ to call the procedure in Java
  • Execute generated method in Java
class BooleanTestIT {
  private static final Logger LOG = LoggerFactory.getLogger(BooleanTestIT.class);

  @Autowired
  private DSLContext dsl;

  @Test
  void booleanWithType() {

    var service = new BooleanWithType();

    final var address = new TRecAddressRecord();

    address.setZip("98765");
    address.setCity("City");
    address.setStreet("Street");
    address.setHouseNo("123");

    service.setPAddress(address);
    service.setPBoolean(true);

    service.execute(dsl.configuration());

    LOG.info("Result: {}", service.getPOutput());
    assertThat(service.getPOutput()).isEqualToIgnoringCase("TRUE");
  }
  • Watch the output when you run the code
  • Java Log
2023-03-22 16:05:54.969 DEBUG 23240 --- [           main] org.jooq.tools.LoggerListener            : Calling routine          : declare "r1" "DEV"."BOOLEAN_TEST"."T_REC_ADDRESS"; "b2" boolean := case ? when 0 then false when 1 then true end; begin "r1"."ZIP" := ?; "r1"."CITY" := ?; "r1"."STREET" := ?; "r1"."HOUSE_NO" := ?; "DEV"."BOOLEAN_TEST"."BOOLEAN_WITH_TYPE" ("P_ADDRESS" => "r1", "P_BOOLEAN" => "b2", "P_OUTPUT" => ?); end;
2023-03-22 16:05:54.972 DEBUG 23240 --- [           main] org.jooq.tools.LoggerListener            : -> with bind values      : declare "r1" "DEV"."BOOLEAN_TEST"."T_REC_ADDRESS"; "b2" boolean := case 1 when 0 then false when 1 then true end; begin "r1"."ZIP" := ?; "r1"."CITY" := ?; "r1"."STREET" := ?; "r1"."HOUSE_NO" := ?; "DEV"."BOOLEAN_TEST"."BOOLEAN_WITH_TYPE" ("P_ADDRESS" => "r1", "P_BOOLEAN" => "b2", "P_OUTPUT" => ?); end;
2023-03-22 16:05:55.102 DEBUG 23240 --- [           main] org.jooq.tools.LoggerListener            : Fetched OUT parameters   : +--------+
2023-03-22 16:05:55.102 DEBUG 23240 --- [           main] org.jooq.tools.LoggerListener            :                          : |P_OUTPUT|
2023-03-22 16:05:55.102 DEBUG 23240 --- [           main] org.jooq.tools.LoggerListener            :                          : +--------+
2023-03-22 16:05:55.102 DEBUG 23240 --- [           main] org.jooq.tools.LoggerListener            :                          : |FALSE   |
2023-03-22 16:05:55.103 DEBUG 23240 --- [           main] org.jooq.tools.LoggerListener            :                          : +--------+
2023-03-22 16:05:55.103  INFO 23240 --- [           main] d.c.betrieb.rest.BooleanTestIT           : Result: FALSE

org.opentest4j.AssertionFailedError: 
expected: "TRUE"
 but was: "FALSE"
  • Database log
ZIP: City	
City: Street
Street: 123
House_no: 1
Boolean: <NULL>
Output: FALSE

jOOQ Version

jOOQ Professional Edition 3.18.0

Database product and version

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Java Version

openjdk 17.0.6 2023-01-17

OS Version

Microsoft Windows [Version 10.0.19044.2728]

JDBC driver name and version (include name if unofficial driver)

com.oracle.database.jdbc:ojdbc8:19.18.0.0
@lukaseder lukaseder added this to the Version 3.19.0 milestone Mar 22, 2023
@lukaseder lukaseder added this to To do in 3.19 Other improvements via automation Mar 22, 2023
@lukaseder
Copy link
Member

Thanks a lot for your detailed report. I will look into this issue soon.

@lukaseder
Copy link
Member

Trying this now. I'm getting some entirely different problem:

13:57:03,009 DEBUG [LoggerListener                ] - Exception                
org.jooq.exception.DataAccessException: SQL [declare
  "r1" "TEST"."P_14839"."R";
  "b2" boolean := case ? when 0 then false when 1 then true end;
begin
  "r1"."V1" := ?;
  "r1"."V2" := ?;
  "TEST"."P_14839"."P" (
    "P_RECORD" => "r1", 
    "P_BOOLEAN" => "b2", 
    "P_OUTPUT" => ?
  );
end;]; ORA-06502: PL/SQL: numerischer oder Wertefehler: Fehler beim Konvertieren von Zeichen zu Zahl
ORA-06512: in Zeile 3

	at org.jooq_3.19.0-SNAPSHOT.ORACLE21C.debug(Unknown Source) ~[?:?]
	at org.jooq.impl.Tools.translate(Tools.java:3467) ~[classes/:?]
	at org.jooq.impl.Tools.translate(Tools.java:3455) ~[classes/:?]
	at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:772) ~[classes/:?]
	at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:611) ~[classes/:?]
	at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:405) ~[classes/:?]
	at org.jooq.impl.Tools.attach(Tools.java:1611) [classes/:?]
	at org.jooq.impl.AbstractRoutine.execute(AbstractRoutine.java:374) [classes/:?]
	at org.jooq.test.oracle.generatedclasses.test.packages.P_14839.p(P_14839.java:39) [test-classes/:?]
	at org.jooq.test.OracleTest.testOracleProcedureWithRecordAndBooleanParameters(OracleTest.java:5582) [test-classes/:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[?:?]
	at jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[?:?]
	at jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[?:?]
	at java.lang.reflect.Method.invoke(Method.java:568) ~[?:?]
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59) [junit-4.13.2.jar:4.13.2]
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56) [junit-4.13.2.jar:4.13.2]
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17) [junit-4.13.2.jar:4.13.2]
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) [junit-4.13.2.jar:4.13.2]
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) [junit-4.13.2.jar:4.13.2]
	at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:61) [junit-4.13.2.jar:4.13.2]
	at org.junit.rules.TestWatcher$1.evaluate(TestWatcher.java:61) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293) [junit-4.13.2.jar:4.13.2]
	at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26) [junit-4.13.2.jar:4.13.2]
	at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:27) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306) [junit-4.13.2.jar:4.13.2]
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413) [junit-4.13.2.jar:4.13.2]
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:93) [.cp/:?]
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:40) [.cp/:?]
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:529) [.cp/:?]
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:756) [.cp/:?]
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:452) [.cp/:?]
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:210) [.cp/:?]
Caused by: java.sql.SQLException: ORA-06502: PL/SQL: numerischer oder Wertefehler: Fehler beim Konvertieren von Zeichen zu Zahl
ORA-06512: in Zeile 3

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:629) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1230) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:511) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:158) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1236) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1819) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1471) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3760) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4135) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4278) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1013) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219) ~[classes/:?]
	at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219) ~[classes/:?]
	at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4690) ~[classes/:?]
	at org.jooq.impl.AbstractRoutine.execute0(AbstractRoutine.java:622) ~[classes/:?]
	at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:582) ~[classes/:?]
	... 37 more
Caused by: oracle.jdbc.OracleDatabaseException: ORA-06502: PL/SQL: numerischer oder Wertefehler: Fehler beim Konvertieren von Zeichen zu Zahl
ORA-06512: in Zeile 3

	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:636) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:563) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1230) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:298) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:511) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:158) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1236) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1819) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1471) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3760) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4135) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4278) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1013) ~[ojdbc11-21.9.0.0.jar:21.9.0.0.0]
	at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219) ~[classes/:?]
	at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:219) ~[classes/:?]
	at org.jooq.impl.Tools.executeStatementAndGetFirstResultSet(Tools.java:4690) ~[classes/:?]
	at org.jooq.impl.AbstractRoutine.execute0(AbstractRoutine.java:622) ~[classes/:?]
	at org.jooq.impl.AbstractRoutine.executeCallableStatement(AbstractRoutine.java:582) ~[classes/:?]
	... 37 more

Though not using the exact same version of the database and JDBC driver as you. Will investigate this right away.

@lukaseder
Copy link
Member

Ah, no, it's the same problem. The problem being that BOOLEAN parameter markers are placed even before RECORD parameter markers, so the order of parameter binding is switched. It would work (by accident) if your routine had this signature:

PROCEDURE boolean_with_type(
  p_boolean IN BOOLEAN
  p_address IN dev.boolean_test.t_rec_address, 
  p_output OUT VARCHAR2
);

@lukaseder
Copy link
Member

lukaseder commented Mar 28, 2023 •
edited

Fixed in jOOQ 3.19.0, 3.18.2 (#14867), 3.17.11 (#14868), and 3.16.17 (#14869)

The latter two backports are reverted. Too much has been fixed in 3.18.2 related to records and associative arrays. A backport is too much regression risk.

3.19 Other improvements automation moved this from To do to Done Mar 28, 2023
lukaseder added a commit that referenced this issue Mar 28, 2023
@lukaseder
[#14839] PL/SQL procedure returns wrong result when combining boolean…
4ab7e0d 
… and record type as input
lukaseder added a commit that referenced this issue Mar 28, 2023
@lukaseder
[#14839] PL/SQL procedure returns wrong result when combining
ca1eb4e 
boolean and record type as input
lukaseder added a commit that referenced this issue Mar 28, 2023
@lukaseder
[#14839] PL/SQL procedure returns wrong result when combining
db7b33a 
boolean and record type as input
lukaseder added a commit that referenced this issue Mar 28, 2023
@lukaseder
Revert "[#14839] PL/SQL procedure returns wrong result when combining…
2106e53 
… boolean and record type as input"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
C: DB: Oracle C: Functionality E: Enterprise Edition E: Professional Edition P: High R: Fixed T: Defect
Projects
No open projects
3.19 Other improvements
Done
Milestone
Version 3.19.0
Development

No branches or pull requests
2 participants
@lukaseder @jthewes