PostgreSQL: "SELECT 123::text" Parser converts to Oracle's CLOB

[Mark L.]

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

[Lukas Eder]

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

--
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.