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.
