JDBC Error with DATE format

[Former Member]

Hello,

we had created a scenario file to jdbc. while sending file to Oracle DB, it shows error as "ORA-01861: literal does not match format string". we checked the literals and white spaces. still we have same problem. so i have doubt on DATE format. we are sending date from PI system as yyyyMMdd which is not supported by Oracle i think. So please provide the DATE format accepted by Oracle and how to pass from SAP PI mapping. it will be appreciable if provide Steps to do in mapping or UDF code sample.

Also in response status we get the error "ORA-00917: missing comma ". So plz let me know how to solve this issue.

Thanks in Advance.

[deepak_shah]

Hi

The date format for oracle is dd-MON-yyyy.

Please refer to blog below

/people/santhosh.kumarv/blog/2008/10/10/life-is-all-about-days-date-in-xi

/people/sarvesh.singh3/blog/2009/02/13/yes-it-is-possible-with-datetrans-function

Regards,

Deepak.

Edited by: Deepak Shah on May 21, 2010 3:35 PM

[former_member187339]

Hi Saran,

>>we are sending date from PI system as yyyyMMdd which is not supported by Oracle i think.

Two things:

1. The field which takes date in PI, make it as string type

2. Do a date conversion so that the date in format yyyyMMdd gets converted to YYYY-MM-DD. this date conversion can be done in graphical mapping of PI with the standard date conversion function

Regards

Suraj

[abhay_rajhans2]

Hi Saran,

Go through below blog. It is pointing towards how to resolve date format issue in JDBC adapter.

/people/alessandro.berta/blog/2005/10/04/datetime-datatypes-and-oracle-database

[Former Member]

Hi Rajhans Abhay,

thanks for reply....but we are not able to change the Data type now (dueto that DT used by many) to use attribute hasQuot . this date format conversion done by PI side or we can do it on Oracle DB also.

In that link Date is hardcoded? could you please provide the stpes needs to be done in mapping in detail (as i am not abel to follow that link.)

[abhay_rajhans2]

Hi Saran,

You can create a new data type and use that data type for this scenario only. This date format conversion is on the PI side.

This date problem is prominent while inserting date in oracle database. And you have do this change in PI end.

You need not to hard code the date. In place of constant (2005-07-14) put your date field in graphical mapping which is passing date to oracle. And you can change date format as per your requirement as yyyyMMdd.

[Former Member]

Hi Rajhans Abhay,

it means that i need to change my Data type and add the "hasQuot" filed for all date fileds and then implement the concat().

So what is the exact DATE format support by Oracle DB? also in PI the Date filed data type is STRING and in my Oracle DB the Date field DataType is DATE. will it casue error?

without adding hasQuot, if i implement the TO_DATE in my mapping as you told, will it works?

Thanks in Advance

Edited by: SARAN on May 21, 2010 10:32 AM

[Former Member]

Hi,

Not a issue with data type.It is not give any error.Just u made date transformation from yyyyMMDD to yyyy-MM-DD.Actual Oracle database date fromat is yyyy-MM-DD.

Thanks

Ravi