Question Re: date conversion error ORA-01830: date format picture ends before converting entire input string in jdbc in Technology Q&A

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

former_member200386 — Wed, 03 Oct 2012 09:01:38 GMT

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

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

Former Member — Wed, 03 Oct 2012 09:40:31 GMT

Hi Pavan,

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

Regards

Praveen

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

Former Member — Wed, 03 Oct 2012 10:30:59 GMT

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.

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

former_member200386 — Wed, 03 Oct 2012 10:42:27 GMT

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

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

Former Member — Wed, 03 Oct 2012 11:20:23 GMT

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

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

former_member200386 — Wed, 03 Oct 2012 12:11:12 GMT

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

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

Former Member — Wed, 03 Oct 2012 14:31:51 GMT

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.

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

baskar_gopalakrishnan2 — Wed, 03 Oct 2012 14:59:06 GMT

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.

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

former_member200386 — Thu, 04 Oct 2012 05:06:20 GMT

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

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

Former Member — Thu, 04 Oct 2012 05:35:55 GMT

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

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

Former Member — Thu, 04 Oct 2012 05:51:54 GMT

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