Hi Manfred, Looks like I have a SQL-Bug in my Oracle integration for source code generation:
java.sql.SQLException: ORA-01427: Unterabfrage für eine Zeile liefert mehr als eine Zeile I'll check into that more in-depth and potentially release a 1.5.9.1 patch for Oracle users Thanks for reporting this Lukas 2011/6/1 Manfred Schäfer <[email protected]>: > Hi Lukas, > > My Oracle Version is 11g 11.1.0.7.0 > > the Keys class is empty: > > > import javax.annotation.Generated; > > import org.jooq.impl.AbstractKeys; > > > /** > * This class is generated by jOOQ. > * > * A class modelling foreign key relationships between tables of the > LOCATIONS2 schema > */ > @Generated(value = "http://jooq.sourceforge.net";, > comments = "This class is generated by jOOQ") > @SuppressWarnings({"unchecked"}) > public class Keys extends AbstractKeys { > > // IDENTITY definitions > > // UNIQUE and PRIMARY KEY definitions > > // FOREIGN KEY definitions > > /** > * No instances > */ > private Keys() {} > } > > I've got one error in the generation log which looks unrelated: > > DEBUG [main] (JooqLogger.java:142) - Adding unique key : > UNIQUE_PLAN_LOCATION (LOCATIONS2.LOCATIONPLAN.FK__LOCATION_KEY) > DEBUG [main] (JooqLogger.java:142) - Executing query : select > "cc1"."CONSTRAINT_NAME" "fk_name", "cc2"."CONSTRAINT_NAME" "uk_name", > "cc1"."TABLE_NAME" "fk_table", "cc2"."TABLE_NAME" "uk_table", > "cc1"."COLUMN_NAME" "fk_column" from "SYS"."ALL_CONSTRAINTS" join > "SYS"."ALL_CONS_COLUMNS" "cc1" on "cc1"."CONSTRAINT_NAME" = > "SYS"."ALL_CONSTRAINTS"."CONSTRAINT_NAME" join > "SYS"."ALL_CONS_COLUMNS" "cc2" on ("cc2"."CONSTRAINT_NAME" = > "SYS"."ALL_CONSTRAINTS"."R_CONSTRAINT_NAME" and "cc2"."POSITION" = > "cc1"."POSITION") where ("SYS"."ALL_CONSTRAINTS"."OWNER" = > 'LOCATIONS2' and "cc1"."OWNER" = 'LOCATIONS2' and "cc2"."OWNER" = > 'LOCATIONS2' and "cc1"."CONSTRAINT_NAME" = (select > "SYS"."ALL_CONSTRAINTS"."CONSTRAINT_NAME" from > "SYS"."ALL_CONS_COLUMNS" join "SYS"."ALL_CONSTRAINTS" on > "SYS"."ALL_CONS_COLUMNS"."CONSTRAINT_NAME" = > "SYS"."ALL_CONSTRAINTS"."CONSTRAINT_NAME" where > ("SYS"."ALL_CONSTRAINTS"."CONSTRAINT_TYPE" = 'R' and > "SYS"."ALL_CONS_COLUMNS"."OWNER" = 'LOCATIONS2' and > "SYS"."ALL_CONS_COLUMNS"."TABLE_NAME" = "cc1"."TABLE_NAME" and > "SYS"."ALL_CONS_COLUMNS"."COLUMN_NAME" = "cc1"."COLUMN_NAME"))) order > by "fk_name" asc, "cc2"."POSITION" asc > DEBUG [main] (JooqLogger.java:142) - Statement executed : Total: > 14.371ms > ERROR [main] (JooqLogger.java:268) - Error while fetching relations > java.sql.SQLException: ORA-01427: Unterabfrage für eine Zeile liefert > mehr als eine Zeile > > at > oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70) > at > oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133) > at > oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206) > at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455) > at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413) > at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034) > at > oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:194) > at > oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:791) > at > oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:866) > at > oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1186) > at > oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3387) > at > oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3431) > at > oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1491) > at > org.jooq.impl.AbstractResultProviderQuery.executeLazy(AbstractResultProviderQuery.java:125) > at > org.jooq.impl.AbstractResultProviderQuery.execute(AbstractResultProviderQuery.java:68) > at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:90) > at > org.jooq.util.oracle.OracleDatabase.loadForeignKeys(OracleDatabase.java:182) > at > org.jooq.util.AbstractDatabase.getRelations0(AbstractDatabase.java:424) > at > org.jooq.util.AbstractDatabase.getRelations(AbstractDatabase.java:308) > at > org.jooq.util.DefaultColumnDefinition.getPrimaryKey(DefaultColumnDefinition.java:141) > at > org.jooq.util.AbstractTableDefinition.getMainUniqueKey(AbstractTableDefinition.java:71) > at org.jooq.util.DefaultGenerator.generate(DefaultGenerator.java:460) > at org.jooq.util.GenerationTool.main(GenerationTool.java:142) > at org.jooq.util.GenerationTool.main(GenerationTool.java:101) > > the DDL statement (reengineered from SQL Developer) > > CREATE TABLE "LOCATIONS"."MTRLFRMTRLRDRTMPLT" > ( > "PK__MATERIALORDERTEMPLATE_ID" NUMBER(20,0) NOT NULL ENABLE, > "FK__MATERIAL_NO" VARCHAR2(256 BYTE), > "ADDTOAREA" VARCHAR2(1 BYTE), > "PK_ID" NUMBER(20,0) NOT NULL ENABLE, > "IGNOREEXISTINGMATERIAL" VARCHAR2(1 BYTE), > "QUANTITY" NUMBER(10,0), > PRIMARY KEY ("PK__MATERIALORDERTEMPLATE_ID", "PK_ID") USING INDEX > PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL > 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 > FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE > "LOCATIONS_DATA" ENABLE, > CONSTRAINT "MTRLFRMTRLRDRTMPLT_MTRL_MTRL" FOREIGN KEY > ("FK__MATERIAL_NO") REFERENCES "LOCATIONS"."MATERIAL" ("PK_NO") ON > DELETE > SET NULL ENABLE, > CONSTRAINT "MTRLFRMTRLRDRTMPLT_MTRLRDRTMPL" FOREIGN KEY > ("PK__MATERIALORDERTEMPLATE_ID") REFERENCES > "LOCATIONS"."MATERIALORDERTEMPLATE" ("PK_ID") ON > DELETE CASCADE ENABLE > ) > PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE > ( > INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 > PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT > ) > TABLESPACE "LOCATIONS_DATA" ; > CREATE INDEX "LOCATIONS"."IDX_MTRLFRMTRLRDRTMPLT_MTRLRDR" ON > "LOCATIONS"."MTRLFRMTRLRDRTMPLT" > ( > "PK__MATERIALORDERTEMPLATE_ID" > ) > PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE > ( > INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 > PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT > ) > TABLESPACE "LOCATIONS_INDEX" ; > CREATE INDEX "LOCATIONS"."IDX_MTRLFRMTRLRDRTMPLT_MTRL_MT" ON > "LOCATIONS"."MTRLFRMTRLRDRTMPLT" > ( > "FK__MATERIAL_NO" > ) > PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE > ( > INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 > PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT > ) > TABLESPACE "LOCATIONS_INDEX" ; > CREATE UNIQUE INDEX "LOCATIONS"."SYS_C005603" ON > "LOCATIONS"."MTRLFRMTRLRDRTMPLT" > ( > "PK__MATERIALORDERTEMPLATE_ID", "PK_ID" > ) > PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE > ( > INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 > PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT > ) > TABLESPACE "LOCATIONS_DATA" ; > > > that was a little bit messy but hopefully it will help you. > > cheers, > > Manfred > > > 2011/5/30 Lukas Eder <[email protected]>: >> Hi Manfred, >> >> This works fine for me both in my Oracle XE development database, and >> in a "full-fledged" 11g database, so I'm guessing it's not a version >> issue here (unless you have some other version than 10g or 11g? >> >> Can you do these things for me: >> >> - Provide me with the generated Keys.java class (if it exists) >> - Provide me with any errors in your generation log, that you might have >> gotten >> - Provide me with the generated Mtrlfrmtrlrdrtmplt.java class >> - Provide me with the definition of the MTRLFRMTRLRDRTMPLT table >> (CREATE TABLE ... statement, including all key specifications) >> >> Cheers >> Lukas >> >> 2011/5/30 Manfred Schäfer <[email protected]>: >>> Hi, >>> >>> i'm generating source code with jooq 1.5.9. The debug output tells me, >>> that JOOQ has correctly identified the primary keys: >>> >>> DEBUG [main] (JooqLogger.java:142) - Adding primary key : >>> SYS_C0013879 (LOCATIONS.MTRLFRMTRLRDRTMPLT.PK__MATERIALORDERTEMPLATE_ID) >>> DEBUG [main] (JooqLogger.java:142) - Adding primary key : >>> SYS_C0013879 (LOCATIONS.MTRLFRMTRLRDRTMPLT.PK_ID) >>> >>> >>> But in the generated code all primary and foreign keys are missing. >>> The generation properties file conatins the line >>> >>> generator.generate.relations=true >>> >>> If i'm using the same libs with my toy XE Database, the generation >>> works as expected!? >>> >>> regards, >>> >>> Manfred >>> >> >
