We are trying to store a record:
DSLContext dsl = DSL.using(conn, SQLDialect.ORACLE12C);
KampartikelRecord kampartikelRecord = dsl.newRecord(KAMPARTIKEL);
kampartikelRecord.setKampagne(387);
kampartikelRecord.setArtnr("090248");
kampartikelRecord.setArtId(5558);
kampartikelRecord.store();
But we get:
org.jooq.exception.DataAccessException: SQL [insert into "TOSCA"."KAMPARTIKEL" ("KAMPAGNE", "ART_ID", "ARTNR") values (?, ?, ?)]; ORA-04043: Objekt "TOSCA" ist nicht vorhanden
at org.jooq_3.12.1.ORACLE12C.debug(Unknown Source)
at org.jooq.impl.Tools.translate(Tools.java:2717)
at org.jooq.impl.DefaultExecuteContext.sqlException(DefaultExecuteContext.java:755)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:383)
at org.jooq.impl.TableRecordImpl.storeInsert0(TableRecordImpl.java:206)
at org.jooq.impl.TableRecordImpl$1.operate(TableRecordImpl.java:177)
at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:130)
at org.jooq.impl.TableRecordImpl.storeInsert(TableRecordImpl.java:173)
at org.jooq.impl.UpdatableRecordImpl.store0(UpdatableRecordImpl.java:196)
at org.jooq.impl.UpdatableRecordImpl$1.operate(UpdatableRecordImpl.java:136)
at org.jooq.impl.RecordDelegate.operate(RecordDelegate.java:130)
at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:132)
at org.jooq.impl.UpdatableRecordImpl.store(UpdatableRecordImpl.java:124)
at ch.tosca.common.repository.JooqRepositoryTest.insertKampartikel(JooqRepositoryTest.java:66)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:567)
at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:50)
at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:47)
at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:26)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:325)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$3.run(ParentRunner.java:290)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:71)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:288)
at org.junit.runners.ParentRunner.access$000(ParentRunner.java:58)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:268)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner.run(ParentRunner.java:363)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.apache.maven.surefire.junit4.JUnit4Provider.execute(JUnit4Provider.java:252)
at org.apache.maven.surefire.junit4.JUnit4Provider.executeTestSet(JUnit4Provider.java:141)
at org.apache.maven.surefire.junit4.JUnit4Provider.invoke(JUnit4Provider.java:112)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.base/java.lang.reflect.Method.invoke(Method.java:567)
at org.apache.maven.surefire.util.ReflectionUtils.invokeMethodWithArray(ReflectionUtils.java:189)
at org.apache.maven.surefire.booter.ProviderFactory$ProviderProxy.invoke(ProviderFactory.java:165)
at org.apache.maven.surefire.booter.ProviderFactory.invokeProvider(ProviderFactory.java:85)
at org.apache.maven.surefire.booter.ForkedBooter.runSuitesInProcess(ForkedBooter.java:115)
at org.apache.maven.surefire.booter.ForkedBooter.main(ForkedBooter.java:75)
Caused by: java.sql.SQLSyntaxErrorException: ORA-04043: Objekt "TOSCA" ist nicht vorhanden
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:456)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:451)
at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1040)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:550)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:268)
at oracle.jdbc.driver.T4C8Odsy.doODSY(T4C8Odsy.java:146)
at oracle.jdbc.driver.T4C8Odsy.doODSYTable(T4C8Odsy.java:126)
at oracle.jdbc.driver.T4CConnection.doDescribeTable(T4CConnection.java:5168)
at oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:4654)
at org.jooq.impl.ProviderEnabledConnection.prepareStatement(ProviderEnabledConnection.java:136)
at org.jooq.impl.SettingsEnabledConnection.prepareStatement(SettingsEnabledConnection.java:125)
at org.jooq.impl.AbstractDMLQuery.prepare0(AbstractDMLQuery.java:859)
at org.jooq.impl.AbstractDMLQuery.prepare(AbstractDMLQuery.java:754)
at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:336)
... 48 more
I've read a GitHub issue about driver incompatibility but Database and Driver ojdbc8 both are version 12.2.0.1.0
What are we doing wrong?
This is a known issue, which has been reported a few times:
I'm still not 100% sure what Oracle server or JDBC bug causes it, but it can be avoided either by not qualifying the table with the schema name, or by avoiding to quote the schema name. Here's how to reproduce it with plain JDBC:
Using this table
CREATE TABLE T (
ID NUMBER(7),
CONSTRAINT pk PRIMARY KEY (ID)
);
Use the Connection.prepareStatement(String, String[]) overload, such as jOOQ does, internally:
try (Connection c = dbSetup.getConnection()) {
try (PreparedStatement s = c.prepareStatement(
"insert into \"TEST\".\"T\" (\"ID\") values (?)", new String[] { "ID" })) {
s.setInt(1, 1);
s.execute ();
}
}
Don't quote the schema name:
try (Connection c = dbSetup.getConnection()) {
try (PreparedStatement s = c.prepareStatement(
"insert into TEST.\"T\" (\"ID\") values (?)", new String[] { "ID" })) {
s.setInt(1, 1);
s.execute ();
}
}
Avoid the qualification entirely:
try (Connection c = dbSetup.getConnection()) {
try (PreparedStatement s = c.prepareStatement(
"insert into \"T\" (\"ID\") values (?)", new String[] { "ID" })) {
s.setInt(1, 1);
s.execute ();
}
}
Settings.renderSchemaSettings.renderQuotedNamesWe'll finally fix this in jOOQ via https://github.com/jOOQ/jOOQ/issues/7962
I've also created a discussion on the Oracle community channel for JDBC: https://community.oracle.com/message/15550855#15550855