Oracle problems when using RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED

[Julian Backes]

Dear all,

we are using jOOQ Pro with Oracle and so far, it has been an amazing experience! Unfortunately, we needed to set withRenderQuotedNames to RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED because we are using multiple different DBMs (Oracle, postgres, SQLServer) with, let's say, heterogenous naming schemes (please don't ask why). So it's not jOOQs fault but our technical debt. This was working fine until now:

When executing a simple query like jooq.select(TABLE).from(TABLE).fetch() we get an Oracle (19C) error saying the from-clause is wrong. When looking at the query generated by jOOQ we have something like "select table.column1 table.column1, table.column2 table.column2... from table", i.e. the aliases for the columns contain dots which seem to be invalid for unquoted names. When using jooq.selectFrom(TABLE) it works fine (there are no aliases) but in this case, we need to add a computed column like jooq.select(TABLE, someCustomColumn).from(TABLE) so we can't use selectFrom.

Is this a bug? Are we doing something wrong? Is there a workaround?

Thanks in advance

Julian

[Lukas Eder]

Hi Julian,

Thanks for your message.

I wonder if you really want to nest your table? This is a feature that a lot of users by accident:

https://www.jooq.org/doc/latest/manual/sql-building/sql-statements/select-statement/select-clause/select-clause-table/

I mean, you can, and in this particular case, override the Settings.namePathSeparator to something else, e.g. "__". But perhaps you'll prefer using a flat projection, such as jooq.select(TABLE.fields()).select(someCustomColumn)?

I hope this helps,

Lukas

--
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 jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/CAPv0rXGH6iGToufybkC%2BHucf_q7v5W6BUcRoO%3DFRCPAv5ZzaGA%40mail.gmail.com.

[julian...@gmail.com]

Hi Lukas,

thanks a lot for your help! Actually I tried TABLE.fields() but we are using Kotlin which results in a compile error for jooq.select(TABLE.fields()):

None of the following functions can be called with the arguments supplied:
public abstract fun select(p0: (MutableCollection<out SelectFieldOrAsterisk!>..Collection<SelectFieldOrAsterisk!>?)): SelectSelectStep<Record!> defined in org.jooq.DSLContext
public abstract fun select(vararg p0: SelectFieldOrAsterisk!): SelectSelectStep<Record!> defined in org.jooq.DSLContext
public abstract fun <T1 : Any!> select(p0: SelectField<TypeVariable(T1)!>!): SelectSelectStep<Record1<TypeVariable(T1)!>!> defined in org.jooq.DSLContext

When I first encountered this issue I didn't really look into the error which I should have been doing, I think :-) I was able to solve it as follows, which is even better to read in my opinion:

jooq.select(*TABLE.fields(), someCustomColumn).......

I hope this is as intended :-)

Thanks again

Julian

[Lukas Eder]

On Tue, Sep 24, 2024 at 6:57 PM julian...@gmail.com <julian...@gmail.com> wrote:

When I first encountered this issue I didn't really look into the error which I should have been doing, I think :-) I was able to solve it as follows, which is even better to read in my opinion:

jooq.select(*TABLE.fields(), someCustomColumn).......

Yes, this is a syntactically better approach in Kotlin, which wouldn't work in Java. It's not different from my suggestion, logically, though.