Hi Mark, Thanks for your message and for your report. This is clearly a bug. Oracle doesn't support casting types as LOB types as per the documentation: https://docs.oracle.com/database/121/SQLRF/functions024.htm#SQLRF00613
See the section: CAST does not directly support any of the LOB data types. [...] By consequence, jOOQ must not render such a cast, but the to_clob(123) function, instead. I have created an issue for this: https://github.com/jOOQ/jOOQ/issues/7442 As discussed on a different channel, the question whether PostgreSQL TEXT should really correspond to Oracle CLOB in this case is debatable, of course. It's a reasonable default type equivalence for many cases - especially DDL. But perhaps not in your particular case. By consequence, there should be (perhaps - still to be designed properly) a new SPI that allows for translating types between dialects on an ad-hoc basis, or globally. This is feature request #7435: https://github.com/jOOQ/jOOQ/issues/7435 Thanks again for reporting the cast issue. Will fix this ASAP, Lukas 2018-04-25 10:13 GMT+02:00 Mark L. <[email protected]>: > Hi, > > The jOOQ parser should convert the following Postgres statement into an > Oracle statement: > SELECT 123::text; > > > The resulting Oracle statement is > select cast(123 as clob) > > Now I am trying to get the result: > ResultQuery<Record> resultQuery = null; > try { > resultQuery = (ResultQuery<Record>) DSL.using(conn, dialect). > parser().parseQuery(sql); > > ResultSet rs = conn.createStatement().executeQuery(queryStr); > > while (rs.next()) > System.out.println(rs.getString(1)); > > } catch (Exception e) { > System.out.println(String.format("%1$9s : ERROR ON PARSING: > %2$s", dialect, e.getMessage())); > } > > > > This works for SQLDialect.POSTGRES but not for SQLDialect.ORACLE: > *ORA-00932 Inconsistent datatypes String and CLOB* > > In my opinion the TEXT casts should be better casted into varchar instead > of CLOB. In that case it would work. > > Is this a bug or a feature? :D > > > > -- > 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.
