Unable to consume oracle.sql.TIMESTAMPTZ

Question

I'm having issues when trying to transform response from Oracle Database, there is a column of datatype TIMESTAMP(6) WITH TIMEZONE

Getting the error below: Invalid property name: shareBytes on class oracle.sql.TIMESTAMPTZ. Validate that the correct setters is present." evaluating expression:

Already tried passing the argument -Doracle.jdbc.J2EE13Compliant=true to the JVM but did not solve the issue as described in this link: https://help.mulesoft.com/s/question/0D52T00005EM9T6SAL/how-to-read-the-value-from-oraclesqltimestamptz-field

I'm using Oracle JDBC Driver com.oracle.jdbc8:12.2.0.1 and mule runtime 4.3.0-20210119

Thanks

Answer 1

I wasn't able to get the JVM argument to help anything, either.

I finally arrived at converting the Timestamp with time zone column to text on the server side, then casting it back to a DateTime in Dataweave.

-- SQL query
SELECT my_id
  to_char(my_date,'yyyy-mm-dd"T"hh24:mi:ss.ff4tzh:tzm') AS my_date
FROM my_table
ORDER BY my_date

For example, the DBMS is storing the value: 14.04.2022 20:27:05.419 +00:00, and the conversion will output 2022-04-14T20:27:05.4198+00:00

That specific output format for the column should allow you to cast it directly as a DateTime in DataWeave:

%dw 2.0
output application/json
---
payload map {
    "id": $.MY_ID,
    "date": $.MY_DATE as DateTime
}

And yields this output:

[
    {
        "id": "1234567",
        "my_date": "2022-04-14T20:27:05.4198Z"
    }
]