date conversion error ORA-01830: date format picture ends before converting entire input string in jdbc

[former_member200386]

Dear expert,

I am doing one scenario where oracle is the receiver. in the mapping  i have field entry_date my source date format is yyyymmdd  & i need to convert this into dd-mon-yy hh:mm:ss.

i used the date tranformation  logic dd-MMM-yy  HH:mm:ss.  and when i tested it's working fine. but when it reaches data base i am getting the  ORA-01830: date format picture ends before converting entire input string  in jdbc  eeror.

i tried the below link

http://scn.sap.com/message/13145628

In UDF which i need to select single values Or queue?

argument   or paramenter?

If we need UDF then tell me what is i need to do. please share the code & step by procedure i never used UDF.

Regards,

pavan

[former_member190624 · Accepted solution]

Hi Pavan,

The error message  saying  that input string is larger than the provided date format . Make sure you are sending the date format which will be expected by DB.

Regards

Hari.

[former_member200386 · Accepted solution]

Hi Hari,

Thanks for your reply the date format of oracle database is dd-mon-yy hh:mi:ss

now in my message mapping i am converting to  that format

my input fromat:yyyymmdd

ouput:dd-MMM-yy HH:mm:ss.

becoz if put dd-mon-yy hh:mi:ss it is giving some error.

when i tried post the data only dd-MMM-yy. it is going & inserting successfully.

if add HH:mm:ss i am getting the  error that i mentioned in my thread.your valuable required

Regards,

pavan

[former_member192238 · Accepted solution]

Pavan,

Ask database guys to insert this dd-MMM-yy HH:mm:ss format using sql query check whethet table is allowing or not.

Regards

Praveen

[former_member200386 · Accepted solution]

Hi Praveen,

Thanks for  your qucik response.

i have one doubt. when i am doing mapping test its executing sucessfully. but in the comm channel i am getting the above error.is is there in problem with PI mapping? even i checked in sxmb_moni it's howing processed successfully. i don't have any clue. please suggest me . mean while i'll check with my db team

Regards,

pavan

[Former Member · Accepted solution]

Hi Praveen,

There is no problem in PI.. The receiver channel is trying to insert the data into your oracle server.  The date which your sending is not inserting in DB, that's why the channel is throwing error.  Check with your DB team to format the length of the field at their end.

Thanks,

Satish.

[baskar_gopalakrishnan2 · Accepted solution]

There is no problem in mapping. The problem lies at the database table definition side. Please ask DBA to check the length assigned for this column.  I think the time portion value exceeds the date field defined in the table.   Since you use date and time in the table , why dont you create varchar2 type in the table to resolve this issue.

[former_member200386 · Accepted solution]

Hi Baskar,

I checked with DB team, they are saying date filed doesn't have any fixed length. they are saying that the date format which we are sending from PI is string format  not a date. so that is the reason it is not inserting.

they are not ready to change the type varchar2. any suggestions. to send the data in this format dd-MMM-yy HH:mm:ss?

Regards,

pavan

[Former Member · Accepted solution]

Hi,

Try to insert the record at the database table directly using SQL editor and check what format of data is allowed/accepted at the table level ..based on that change your mapping and send the same format..

HTH

Rajesh

[Former Member]

Hi,

have you provided the date format in the Advanced tab as below?

first you try with the only date and explore TIME and TIMESTAMP options..

[former_member192238]

Hi Pavan,

Check with database guys the field which receives the date in oracle was given the length which PI sending.

Regards

Praveen