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.
