[DBOutputTable] Problem with date format (ORA-01847)

CloverDX Platform
1

Hi,

This is my .csv file

“PRIMARY_KEY_1”;“13/02/16”;“USER1”;“17/02/16”;“USER2”

And its metadata:


<Field name="PRIMARY_KEY" nullable="false" type="string"/>
<Field name="USER_CREATE" type="string"/>
<Field format="dd/MM/yy" name="DATE_CREATE" type="date"/>
<Field name="USER_MODIF" type="string"/>
<Field eofAsDelimiter="true" format="dd/MM/yy" name="DATE_MODIF" type="date"/>

In “Query URL” property, I set “update.sql” containing:

UPDATE "XXXX"."MY_TABLE" SET "DATE_CREATE"=?, "USER_MODIF"=?, "DATE_MODIF"=? WHERE "PRIMARY_KEY"=?

In “Field Mapping” property, I set:

$PRIMARY_KEY:=“PRIMARY_KEY”;$USER_CREATE:=“USER_CREATE”;$DATE_CREATE:=“DATE_CREATE”;$USER_MODIF:=“USER_MODIF”;$DATE_MODIF:=“DATE_MODIF”

When I execute the graph, I have this error

ORA-01847: day of month must be between 1 and last day of month

But when I debug the edge in the entry port of DBOutputTable, the dates are correct!
I see “13/02/16” and “17/02/16” like my .csv file.

So why Oracle doesn’t like the format of date?
How to resolve this error?
Thanks for your help.

2

I don’t know if it’s a problem with oracle native jdbc driver, or missing jar for i18n, or NLS parameters (that I don’t even know where to set up in CloverETL)…

Is it just possible to get a working sample which play with different date format (with and without type convertion)
“dd/MM/yyyy”;“dd/MM/yy HH:mm:ss”;“timestamp”;“timestamp”;“dd/MM/yy HH:mm:ss”
“25/02/2014”;“24/02/14 17:23:00”;“1456441106”;“1456441116”;“24/02/14 17:12:00”

And insert it to an oracle table with 5 columns with these formats for each:
DATE, DATE, TIMESTAMP, DATE, TIMESTAMP

So insert “dd/MM/yyyy” in DATE type, “dd/MM/yy HH:mm:ss” in DATE type, “timestamp” in TIMESTAMP type, “timestamp” in DATE type and “dd/MM/yy HH:mm:ss” in TIMESTAMP type.

Thanks for your help.

3

Hi

I’m not able to reproduce the issue. When the Designer is provided with an invalid date, it would be able to catch such date before it gets into the database. Some NLS parameters can be configured in the metadata editor (advanced properties). However, if you change the NLS in CloverETL, it won’t make any difference (in this case), since the CloverETL uses internal Java date format while writing to the database. Changing NLS parameters is useful when you want to see localized data on the edges or when you want to write it into some text file (e.g. *.csv). Since the example you provided in the first post is working for me, I don’t think there is a point in providing a working sample graph. I think the issue might be caused by the graph or other items that come into the process.

In order to resolve the issue, please provide me with the following.

1. Your graph file (*.grf) with its externalized items (metadata etc.). Please, feel free to remove any sensitive information (e.g. credentials).
2. Create statement of your database table.
3. Is this a local project (Designer only) or a CloverETL Server project?
4. What version of the Designer and CloverETL Server (if any) are you using?
5. What version of the Oracle database are you using?
6. What version of the JDBC driver are you using?
7. What Java version are you using?

Best regards