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 On Wed, Sep 12, 2018 at 7:37 PM <[email protected]> wrote: > 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! > > -- > 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 [email protected]. > For more options, visit https://groups.google.com/d/optout. > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
