insertInto()...returning() clause works on MySQL but fails on Oracle 11g with error ORA-04043

[Paul Hamer]

Hi,

We're using jOOQ Pro 3.9.5 and recently we migrated from MySQL to Oracle 11g, which somehow broke my insertInto()...returning() statements.

Here's my code:

Record r = dsl.insertInto(MPS_PEOPLE)
                .set(MPS_PEOPLE.PPLE_ID, Sequences.MPS_PPLE_SEQ.nextval())  <-- only this line was added (in MySQL this used to be an auto-increment column)
                .set(MPS_PEOPLE.OPERATIONAL_INITIALS, atco.getInitials())
                .set(MPS_PEOPLE.FIRST_NAME, atco.getFirstName())
                .set(MPS_PEOPLE.SURNAME, atco.getSurName())
                .set(MPS_PEOPLE.EMAIL_ADDRESS, atco.getEmail())
                .set(MPS_PEOPLE.GENDER, "M")
                .set(MPS_PEOPLE.BIRTH_DATE, new Date(0))
                .set(MPS_PEOPLE.START_DATE, new Date(0))
                .set(MPS_PEOPLE.REPORTING_MANAGER, "N")
                .set(MPS_PEOPLE.COUNTERSIGNING_MANAGER, "N")
                .set(MPS_PEOPLE.AUTOMATIC_INPUT, "N")
                .set(MPS_PEOPLE.CREATED_BY, "1")
                .set(MPS_PEOPLE.CREATED_DATE, new Date(System.currentTimeMillis()))
                .set(MPS_PEOPLE.MODIFIED_BY, "1")
                .set(MPS_PEOPLE.MODIFIED_DATE, new Date(System.currentTimeMillis()))
                .returning(MPS_PEOPLE.PPLE_ID)
                .fetchOne();
int id = r.getValue(MPS_PEOPLE.PPLE_ID).intValue();

This returns the following error:

org.jooq.exception.DataAccessException: SQL [insert into "MPS"."MPS_PEOPLE" ("PPLE_ID", "OPERATIONAL_INITIALS", "FIRST_NAME", "SURNAME", "EMAIL_ADDRESS", "GENDER", "BIRTH_DATE", "START_DATE", "REPORTING_MANAGER", "COUNTERSIGNING_MANAGER", "AUTOMATIC_INPUT", "CREATED_BY", "CREATED_DATE", "MODIFIED_BY", "MODIFIED_DATE") values ("MPS"."MPS_PPLE_SEQ".nextval, ?, ?, ?, ?, ?, cast(? as date), cast(? as date), ?, ?, ?, ?, cast(? as date), ?, cast(? as date))]; ORA-04043: object "MPS" does not exist

However, running that query manually (of course with actual values in place of the '?') using Oracle SQL Developer works fine.

Restructuring the code to the following works fine, but it requires an additional roundtrip to the database to get the generated id...

int id = dsl.select(Sequences.MPS_PPLE_SEQ.nextval()).fetchOne().value1().intValue();
int count = dsl.insertInto(MPS_PEOPLE)
                .set(MPS_PEOPLE.PPLE_ID, (long) id)
                .set(MPS_PEOPLE.OPERATIONAL_INITIALS, atco.getInitials())
                .set(MPS_PEOPLE.FIRST_NAME, atco.getFirstName())
                .set(MPS_PEOPLE.SURNAME, atco.getSurName())
                .set(MPS_PEOPLE.EMAIL_ADDRESS, atco.getEmail())
                .set(MPS_PEOPLE.GENDER, "M")
                .set(MPS_PEOPLE.BIRTH_DATE, new Date(0))
                .set(MPS_PEOPLE.START_DATE, new Date(0))
                .set(MPS_PEOPLE.REPORTING_MANAGER, "N")
                .set(MPS_PEOPLE.COUNTERSIGNING_MANAGER, "N")
                .set(MPS_PEOPLE.AUTOMATIC_INPUT, "N")
                .set(MPS_PEOPLE.CREATED_BY, "1")
                .set(MPS_PEOPLE.CREATED_DATE, new Date(System.currentTimeMillis()))
                .set(MPS_PEOPLE.MODIFIED_BY, "1")
                .set(MPS_PEOPLE.MODIFIED_DATE, new Date(System.currentTimeMillis()))
                .execute();

Any idea what I'm doing wrong?

Kind regards,

Paul Hamer

[Lukas Eder]

Hi Paul,

Thank you very much for your enquiry. This is very strange. The error message seems to indicate that the schema "MPS" has not been found. Does the user you're connecting to the database with have access to the "MPS" schema? I.e. did you use the same user on your JDBC connection as you did in SQL Developer?

To verify if this is really the problem, you could specify Settings.renderSchema = false, which would prevent jOOQ from generating the schema prefix on all tables.

Best Regards,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Paul Hamer]

Hi Lucas,

Thanks for your quick reply.

The issue should not be permission related, as the 2nd piece of code that I quoted works perfectly. Also, in SQL Developer I'm indeed using the same user.

However, I did give your suggestion a try (setting renderSchema to false), and it indeed fixes the issue! A bit strange, but great! Can you explain it maybe?

As a bonus, this also solved the inconvenience that I ran in to every time a wanted to connect to a different DB with a different name  (ie. MPS1 instead of MPS)... it would require regenerating all the jOOQ code. With renderSchema on false, that is no longer needed. Nice!

Thanks a lot & keep up the awesome work!

Paul

[Lukas Eder]

Hi Paul,

Thanks for your feedback. Interesting, I'll have to try running this code on my side. The RETURNING clause translates to a JDBC call to getGeneratedKeys(), which does some things behind the scenes inside of the Oracle JDBC driver. Perhaps, that usage is incompatible when referencing sequences in the VALUES clause - or there might have been some missing support for this usage in 11g. In order for me to try to reproduce this, would you mind telling me:

- The ojdbc version you're using

- The server version you're using

Indeed, when you work on different schemas, turning off the rendering of schemata can help. Alternatively, you can prevent the generation of the schema entirely by specifying <outputSchemaToDefault/> in the code generator. For more info about multitenancy and schema / table mapping, please refer to the following sections of the manual:

- https://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/settings-render-mapping

- https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-catalog-and-schema-mapping

Thanks,

Lukas

--

[Paul Hamer]

Hi Lucas,

Sure no problem:

ojdbc: Oracle ojdbc8 v12.2.0.1

server: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

Hope this helps. Thanks for the pointers on working with different schemas, much appreciated.

Regards,

Paul

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Paul Hamer]

Oops... keep calling you Lucas, sorry Lukas ;-)

[Lukas Eder]

Hi Paul,

Thanks for the update. Interesting, perhaps it could be related to the combination of a 12.2 driver with a 11.2 database. I'll check this ASAP and keep you posted.

Regards,

Lukas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Lukas Eder]

Hi Paul,

Finally an update from my side on this issue - in case you're still encountering it. Another client has investigated this further and found that the problem seems to relate to a driver / server version mismatch. According to the Oracle documentation, these are the correct server versions / driver versions / JDK versions combinations:

Oracle Database version	JDBC Jar files specific to the release
12.2 or 12cR2	ojdbc8.jar with JDK 8
12.1 or 12cR1	ojdbc7.jar with JDK 7 and JDK 8 ojdbc6.jar with JDK 6
11.2 or 11gR2	ojdbc6.jar with JDK 6, JDK 7, and JDK 8  (Note: JDK7 and JDK8 are supported in 11.2.0.3 and 11.2.0.4 only) ojdbc5.jar with JDK 5

See:

http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-faq-090281.html#01_03_1

Since you're using 11g, you should probably also use ojdbc6, not ojdbc8. The other client reported that on 12.1 using ojdbc8, this fully qualified sequence in INSERT statement issue was the only issue they have encountered, and they cannot backport the JDBC driver because of the requirements imposed by their Weblogic versions.

I hope this helps,

Lukas

[Paul Hamer]

Hi Lukas,

Thank you for coming back to this. I have indeed heard/read elsewhere that the Oracle OJDBC drivers are not fully backward compatible to their own Oracle databases. But, as you suggested back when I posted my issue, specifying Settings.renderSchema = false solved the problem. We haven't had any issues since then (still using ojdbc8 + 11g). Since we plan to upgrade to Oracle 12.2 before releasing our software, we intend to stick to ojdbc8.

Cheers,

Paul

[a.fulg...@gmail.com]

Hi all,

I am leaving this info here, as googling "ORA-04043 ojdbc 12.2 returning clause" leads pretty quickly to this thread (and accidentally, the issue(?) persist via jOOQ).

Apparently there is an inconsistency in the new driver. Namely, I observe the following behaviour:

// schema with quotes, with returning clause -> does not work with driver 12.2 'ORA-04043: object "TESTER" does not exist', but works with driver 12.1

connection.prepareStatement("insert into \"TESTER\".\"JDBC_TEST\" (\"ID\", \"BLUBB\") values (?, ?)", new String[]{"ID"});

// schema with quotes, without returning clause -> OK with both drivers

connection.prepareStatement("insert into \"TESTER\".\"JDBC_TEST\" (\"ID\", \"BLUBB\") values (?, ?)");

// schema without quotes, with returning clause -> OK with both drivers

connection.prepareStatement("insert into TESTER.\"JDBC_TEST\" (\"ID\", \"BLUBB\") values (?, ?)", new String[]{"ID"});

// schema without quotes, without returning clause -> OK with both drivers

connection.prepareStatement("insert into TESTER.\"JDBC_TEST\" (\"ID\", \"BLUBB\") values (?, ?)");

Investigation with Oracle ongoing.

Cheers,

Daniel