Hi Ben, Thanks for the update. Indeed, more sophisticated usages of jOOQ API are not easy to handle via the jOOQ DSL as it is implemented right now. In this case, the InsertReturningStep type would need to be inherited only conditionally, based on the Oracle dialect, after the InsertValuesStep's select() call.
In fact, we could introduce additional ReturningSteps that could have different @Support annotations on their returning() methods. At least, this way, this limitation would be better documented. I'll need to think about this a little more, as other INSERT clauses are flawed in similar ways... Cheers, Lukas 2015-12-22 13:38 GMT+01:00 Ben Hood <[email protected]>: > Hi Lukas, > > Many thanks for following up on this. I had come to the same conclusion > about the simplification you made - the original statement I submitted had > a bug in it that got mixed up in conjunction with the returning clause. > > Also thanks for clarifying the server side limitations of the INSERT > RETURNING clause. I guess these are things that would be very hard for JOOQ > to do static checking for - really this is only something the DB can assess > at runtime. > > Cheers, > > Ben > > On Tue, Dec 15, 2015 at 3:33 PM, Lukas Eder <[email protected]> wrote: > >> Hi Ben, >> >> I've done some further analysis and it appears that this is not supported >> on a SQL / PL/SQL level: >> http://stackoverflow.com/q/28837523/521799 >> >> The JDBC driver converts your regular INSERT statement into an INSERT >> RETURNING statement, which exists in PL/SQL, but is supported only for >> INSERT .. VALUES, not INSERT .. SELECT: >> >> [image: Inline-Bild 1] >> >> See also: >> - >> https://docs.oracle.com/database/121/SQLRF/img_text/single_table_insert.htm >> - https://docs.oracle.com/database/121/SQLRF/statements_9014.htm#i2079995 >> >> The same happens when doing this in PL/SQL directly: >> >> set serveroutput on >> declare >> v_id t_triggers.id%type; >> v_id_generated t_triggers.id_generated%type; >> v_counter t_triggers.counter%type; >> begin >> insert into "TEST"."T_TRIGGERS" ("COUNTER") >> select 0 >> from dual >> returning id, id_generated, counter >> into v_id, v_id_generated, v_counter; >> >> dbms_output.put_line('1 : ' || v_id); >> dbms_output.put_line('2 : ' || v_id_generated); >> dbms_output.put_line('3 : ' || v_counter); >> end; >> / >> >> I'm getting >> >> ORA-06550: line 9, column 13: >> PL/SQL: ORA-00933: SQL command not properly ended >> >> I'm afraid that this simply doesn't work with Oracle :-/ >> >> Lukas >> >> 2015-12-11 16:25 GMT+01:00 Ben Hood <[email protected]>: >> >>> For reference, the following simplification does work: >>> >>> Optional<XSubRecord> record = >>> ctx.insertInto(X_SUB, X_SUB.TENANT, >>> X_SUB.UPSTREAM,X_SUB.START_DATE, X_SUB.END_DATE). >>> values(bigTenant, bigUpstream, lowerBound, upperBound). >>> returning(X_SUB.ID). >>> fetchOptional(); >>> >>> This is what it generated: >>> >>> 2015-12-11 15:24:09,880 [qtp2044211046-26] DEBUG o.j.t.LoggerListener >>> -> with bind values : insert into "SUBSCRIPTIONS"."X_SUB" >>> ("TENANT", "UPSTREAM", "START_DATE", "END_DATE") values (1, 13655, >>> timestamp '2013-07-11 05:40:49.547', timestamp '2015-05-20 >>> 14:04:56.113') >>> 2015-12-11 15:24:09,896 [qtp2044211046-26] DEBUG o.j.t.LoggerListener >>> Affected row(s) : 1 >>> 2015-12-11 15:24:09,897 [qtp2044211046-26] DEBUG o.j.t.StopWatch Query >>> executed : Total: 28.284ms >>> 2015-12-11 15:24:09,904 [qtp2044211046-26] DEBUG o.j.t.StopWatch >>> Finishing : Total: 6.219ms >>> 2015-12-11 15:24:09,905 [qtp2044211046-26] DEBUG o.j.t.LoggerListener >>> Fetched result : +----+ >>> 2015-12-11 15:24:09,905 [qtp2044211046-26] DEBUG o.j.t.LoggerListener >>> : | ID| >>> 2015-12-11 15:24:09,906 [qtp2044211046-26] DEBUG o.j.t.LoggerListener >>> : +----+ >>> 2015-12-11 15:24:09,906 [qtp2044211046-26] DEBUG o.j.t.LoggerListener >>> : | 4| >>> 2015-12-11 15:24:09,906 [qtp2044211046-26] DEBUG o.j.t.LoggerListener >>> : +----+ >>> 2015-12-11 15:24:09,909 [qtp2044211046-26] DEBUG o.j.t.StopWatch >>> Finishing : Total: 40.949ms, +12.665ms >>> >>> Is maybe the subquery causing an issue? >>> >>> >>> >>> On Fri, Dec 11, 2015 at 3:16 PM, Lukas Eder <[email protected]> >>> wrote: >>> > OK, no then it's not related. I was perhaps a bit too quick with that >>> > assumption. >>> > >>> > I'll try to reproduce this issue and get back to you with additional >>> > findings. Perhaps it is related to the RETURNING clause emulation in >>> jOOQ... >>> > >>> > 2015-12-11 16:12 GMT+01:00 Ben Hood <[email protected]>: >>> >> >>> >> OK, so I renamed the table to X_SUB but I still get the same behavior: >>> >> >>> >> SQL [insert into "SUBSCRIPTIONS"."X_SUB" ("TENANT", "UPSTREAM", >>> >> "START_DATE", "END_DATE") select ?, ?, ?, ? from >>> >> "SUBSCRIPTIONS"."X_SUB" where not exists (select ? from >>> >> "SUBSCRIPTIONS"."X_SUB" where ("SUBSCRIPTIONS"."X_SUB"."TENANT" = ? >>> >> and "SUBSCRIPTIONS"."X_SUB"."UPSTREAM" = ? and >>> >> "SUBSCRIPTIONS"."X_SUB"."START_DATE" = ?))]; ORA-00933: SQL command >>> >> not properly ended >>> >> >>> >> Should I maybe get rid of the SUB completely, i.e. just call the table >>> >> X for example? >>> >> >>> >> Or are there potentially some JDBC settings that I should be setting >>> >> when starting a transaction? >>> >> >>> >> >>> >> On Fri, Dec 11, 2015 at 2:55 PM, Lukas Eder <[email protected]> >>> wrote: >>> >> > Yes - It's weird, I know, but it just rang a bell... >>> >> > >>> >> > 2015-12-11 15:51 GMT+01:00 Ben Hood <[email protected]>: >>> >> >> >>> >> >> Hey Lukas, >>> >> >> >>> >> >> If I understand you correctly, you're suggesting to rename the >>> table >>> >> >> and re-execute the code to see if the behavior is consistent when >>> >> >> using a different table name? If so, I'll do that and report back. >>> >> >> >>> >> >> Ben >>> >> >> >>> >> >> On Fri, Dec 11, 2015 at 2:46 PM, Lukas Eder <[email protected]> >>> >> >> wrote: >>> >> >> > Hi Ben, >>> >> >> > >>> >> >> > Oracle SQL doesn't really support the RETURNING clause. It >>> exists in >>> >> >> > PL/SQL, >>> >> >> > but it cannot be used in SQL directly. However, the ojdbc driver >>> >> >> > supports >>> >> >> > Statement.getGeneratedKeys(), which is what jOOQ calls behind the >>> >> >> > scenes. >>> >> >> > >>> >> >> > I don't really see any syntax problem. However, I've run into >>> similar >>> >> >> > issues >>> >> >> > in the past when the schema name is the same as the table name - >>> >> >> > although >>> >> >> > only with packages, not with tables. Just to be sure, does this >>> >> >> > problem >>> >> >> > also >>> >> >> > appear when the table is named differently? >>> >> >> > >>> >> >> > Cheers, >>> >> >> > Lukas >>> >> >> > >>> >> >> > 2015-12-11 15:38 GMT+01:00 Ben Hood <[email protected]>: >>> >> >> >> >>> >> >> >> Sorry, I forgot to include the relevant DDL: >>> >> >> >> >>> >> >> >> CREATE TABLE subscriptions ( >>> >> >> >> id NUMBER(19) GENERATED BY DEFAULT AS IDENTITY PRIMARY >>> >> >> >> KEY, >>> >> >> >> tenant NUMBER(19) NOT NULL, >>> >> >> >> upstream NUMBER(19) NOT NULL, >>> >> >> >> start_date TIMESTAMP NOT NULL, >>> >> >> >> end_date TIMESTAMP, >>> >> >> >> period FOR subscription (start_date, end_date) >>> >> >> >> ); >>> >> >> >> >>> >> >> >> >>> >> >> >> On Fri, Dec 11, 2015 at 2:35 PM, Ben Hood <[email protected]> >>> >> >> >> wrote: >>> >> >> >> > Hi Lukas, >>> >> >> >> > >>> >> >> >> > I'm trying to an UPSERT without using the MERGE syntax >>> because I >>> >> >> >> > would >>> >> >> >> > like to get the generated key back from the DB. >>> >> >> >> > >>> >> >> >> > However, this results with the error: ORA-00933: SQL command >>> not >>> >> >> >> > properly ended >>> >> >> >> > >>> >> >> >> > This is what the DSL looks like: >>> >> >> >> > >>> >> >> >> > Optional<SubscriptionsRecord> record = >>> >> >> >> > ctx.insertInto(SUBSCRIPTIONS, SUBSCRIPTIONS.TENANT, >>> >> >> >> > SUBSCRIPTIONS.UPSTREAM,SUBSCRIPTIONS.START_DATE, >>> >> >> >> > SUBSCRIPTIONS.END_DATE). >>> >> >> >> > select( >>> >> >> >> > ctx.select(DSL.val(bigTenant), DSL.val(bigUpstream), >>> >> >> >> > DSL.val(lowerBound), DSL.val(upperBound)). >>> >> >> >> > from(SUBSCRIPTIONS). >>> >> >> >> > whereNotExists( >>> >> >> >> > ctx.select(DSL.val(1)). >>> >> >> >> > from(SUBSCRIPTIONS). >>> >> >> >> > where(uniqueKeyCondition) >>> >> >> >> > )). >>> >> >> >> > returning(SUBSCRIPTIONS.ID). >>> >> >> >> > fetchOptional(); >>> >> >> >> > >>> >> >> >> > And this is the SQL that is generated: >>> >> >> >> > >>> >> >> >> > SQL [insert into "SUBSCRIPTIONS"."SUBSCRIPTIONS" ("TENANT", >>> >> >> >> > "UPSTREAM", "START_DATE", "END_DATE") select ?, ?, ?, ? from >>> >> >> >> > "SUBSCRIPTIONS"."SUBSCRIPTIONS" where not exists (select ? >>> from >>> >> >> >> > "SUBSCRIPTIONS"."SUBSCRIPTIONS" where >>> >> >> >> > ("SUBSCRIPTIONS"."SUBSCRIPTIONS"."TENANT" = ? and >>> >> >> >> > "SUBSCRIPTIONS"."SUBSCRIPTIONS"."UPSTREAM" = ? and >>> >> >> >> > "SUBSCRIPTIONS"."SUBSCRIPTIONS"."START_DATE" = ?))]; >>> ORA-00933: >>> >> >> >> > SQL >>> >> >> >> > command not properly ended >>> >> >> >> > >>> >> >> >> > It looks like the RETURNING INTO is not properly rendered. >>> >> >> >> > >>> >> >> >> > Am I somehow misusing the fluent DSL vis a vis RETURNING? >>> >> >> >> > >>> >> >> >> > Cheers, >>> >> >> >> > >>> >> >> >> > Ben >>> >> >> >> >>> >> >> >> -- >>> >> >> >> 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. >>> >> >> >>> >> >> -- >>> >> >> 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. >>> >> >>> >> -- >>> >> 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. >>> >>> -- >>> 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. >> > > -- > 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.
