Oracle 11g, multiple schemata: Error ORA-04043 when inserting data

[rega...@gmail.com]

Hi all

I have two schemas (users) in my Oracle 11g database.

The jOOQ generator is run by maven, configured like this:

<generator>
    <database>
        <name>org.jooq.meta.oracle.OracleDatabase</name>
        <includes>USER1.*|USER2.*</includes>
        <excludes />
        <schemata>
            <schema>
                <inputSchema>USER1</inputSchema>
            </schema>
            <schema>
                <inputSchema>USER2</inputSchema>
            </schema>
        </schemata>
    </database>
    <target>
        <packageName>my.application.jooq</packageName>
        <directory>src/main/java</directory>
        <clean>true</clean>
    </target>
    <generate>
        <jpaAnnotations>true</jpaAnnotations>
        <validationAnnotations>true</validationAnnotations>
        <springAnnotations>true</springAnnotations>
        <pojos>true</pojos>
        <daos>true</daos>
    </generate>
</generator>

Generation works, and the jOOQ classes are created in two packages "user1" and "user2".

When using the generated classes, SELECT queries work and return data from the two schemata, using the same DSL.

But when inserting data into a table in any of the two schemata, I get an exception:
java.sql.SQLSyntaxErrorException: ORA-04043: object $SCHEMANAME does not exist
(where $SCHEMANAME is either "USER1" or "USER2", depending on where I wanted to insert).

The debug log shows this:

jOOQ; bad SQL grammar [insert into "$SCHEMANAME"."$TABLENAME" ("COL1", "COL2", "COL3", "COL_PRIMARYKEY") values (?, ?, ?, "$SCHEMANAME"."$TABLENAMESEQ".nextval)]; nested exception is java.sql.SQLSyntaxErrorException: ORA-04043: object "$SCHEMANAME" does not exist

Running the exactly same query (obviously substituting the ?'s) on the console works without problems.

The connecting user is USER1, which was granted all privileges during creation:

CREATE TABLESPACE user1_data DATAFILE '/u01/app/oracle/oradata/XE/user1_data_01.dbf' size 64M;
CREATE TABLESPACE user2_data DATAFILE '/u01/app/oracle/oradata/XE/user2_data_01.dbf' size 64M;
CREATE USER user1 IDENTIFIED BY XXX default tablespace user1_data quota unlimited on user1_data;
CREATE USER user2 IDENTIFIED BY YYY default tablespace user2_data quota unlimited on user2_data;
GRANT connect TO user1;
GRANT execute ON dbms_crypto TO user1;
GRANT ALL PRIVILEGES TO user1;

Before adding the second schema USER2, the application worked fine with USER1, no code changes except for regenerating the jOOQ classes and adapting the includes
to match the new packages were made.

The insert goes along the lines of:

my.application.jooq.rpm.tables.pojos.Tablename TablenamePojo = ...
Record record = dsl.insertInto(my.application.jooq.user2.Tables.TABLENAME)
    .set(
        dsl.newRecord(my.application.jooq.user2.Tables.TABLENAME, TablenamePojo)
    )
    .set(TABLENAME.COL_PRIMARYKEY, Sequences.TABLENAMESEQ.nextval())
    .returning()
    .fetchOne()
return record.into(Tablename.class)

Is there anything else I need to do for using two schemata in parallel?

Thanks all!

[Lukas Eder]

Hello,

Thanks a lot for your report. This ORA-04043 issue came up on this mailing list a few times. There seems to be a bug somewhere between the JDBC driver and the server side SQL parser in Oracle, that does not allow for sequences to be fully qualified in some situations. See the previous discussions for details:

• https://groups.google.com/d/msg/jooq-user/wsrXB5jZmmI/_Ic8wAVaBAAJ
  
• https://groups.google.com/d/msg/jooq-user/vmxNmoPDC5c/5dZfNY2SAAAJ
  

This doesn't happen with all Oracle versions and/or ojdbc driver versions. The workaround is to turn off qualification of at least the sequence, if not all the objects in the statement. You can "unqualify" the sequence name by using DSL.sequence():

https://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#sequence-org.jooq.Name-org.jooq.DataType-

For example:

DSL.sequence(Sequences.TABLENAMESEQ.getUnqualifiedName(), Sequences.TABLENAMESEQ.getDataType())

Or, you turn of qualification of objects using Settings.renderSchema = false

I hope this helps,

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+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[rega...@gmail.com]

Hi Lukas

Thanks for your prompt reply.

I have tried to modify my code as such:

my.application.jooq.rpm.tables.pojos.Tablename TablenamePojo = ...
Record record = dsl.insertInto(my.application.jooq.user2.Tables.TABLENAME)
 .set(
 dsl.newRecord(my.application.jooq.user2.Tables.TABLENAME, TablenamePojo)
 )

 .set(TABLENAME.COL_PRIMARYKEY, DSL.sequence(Sequences.TABLENAMESEQ.getUnqualifiedName(), Sequences.TABLENAMESEQ.getDataType()).nextval())
 .returning()
 .fetchOne()
return record.into(Tablename.class)

But the error still occurs.

The only thing that seems to help is downgrading the jdbc driver to ojdbc6. After that, all works as expected.
Unfortunately this is not a very robust approach, as there is no way to tell if the issue will reappear in other driver/db combinations.

As for the other approach ("Settings.renderSchema = false"): Is this something I need to specify in the generator, or is this a runtime configuration?
I did not find that setting anywhere in the XML schema used for configuring the generator. 
How would I use that?

Best, Markus

[Lukas Eder]

Hi Markus,

I'm very sorry, I hadn't realised that there was still an open question in your previous email

On Thu, Sep 13, 2018 at 10:52 AM <rega...@gmail.com> wrote:

But the error still occurs.

The only thing that seems to help is downgrading the jdbc driver to ojdbc6. After that, all works as expected.
Unfortunately this is not a very robust approach, as there is no way to tell if the issue will reappear in other driver/db combinations.

As for the other approach ("Settings.renderSchema = false"): Is this something I need to specify in the generator, or is this a runtime configuration?
I did not find that setting anywhere in the XML schema used for configuring the generator. 
How would I use that?

Settings is something you pass to your jOOQ Configuration. You can find the relevant documentation here:

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

[Lukas Eder]

For the record, and anyone finding this discussion later, jOOQ 3.13 will deploy a workaround for this ojdbc bug that can be activated / deactivated using a new flag Settings.applyWorkaroundFor7962. It is turned on by default, and affects only the ORACLE dialect.

The problem happens when using:

• An INSERT statement
• With Connection.prepareStatement(String, String[]), i.e. only when fetching generated keys
• Using a qualified table name
• Using a quoted table qualifier

Any workaround that prevents any of the above (turning off fetching of generated keys, not qualifying the table name, not quoting the table identifier) will prevent the exposure of the ojdbc bug. So, a workaround can already be deployed prior to jOOQ 3.13, using relevant Settings. The above Settings.applyWorkaroundFor7962 will work around the issue by default, and can be turned off in case someone does not use a problematic ojdbc version, but needs to quote schema identifiers.

For more details, see:

https://github.com/jOOQ/jOOQ/issues/7962

Thanks,

Lukas