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

Support/help with CloverETL (4.9) and CloverDX (5.0 or newer) implementation problems

cassydeb
Posts: 7
Joined: Tue Feb 02, 2016 8:06 am

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

Postby cassydeb » Wed Feb 17, 2016 8:55 am

Hi,

This is my .csv file
"PRIMARY_KEY_1";"13/02/16";"USER1";"17/02/16";"USER2"


And its metadata:

Code: Select all

<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:

Code: Select all

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.

cassydeb
Posts: 7
Joined: Tue Feb 02, 2016 8:06 am

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

Postby cassydeb » Fri Feb 26, 2016 12:33 am

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.

cholastal
Posts: 135
Joined: Tue Sep 01, 2015 1:22 pm

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

Postby cholastal » Wed Mar 02, 2016 2:14 pm

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

---
Lukas Cholasta
CloverCARE Support
CloverDX

Visit us online at http://www.cloverdx.com