issue with Date format in SP

vinodkumards · Post by **vinodkumards** » Tue Jun 04, 2013 7:36 pm

Hi,

I get the following error while calling an oracle stored procedure using Stored procedure stage

[IBM(DataDirect OEM)][ODBC Oracle Wire Protocol driver][Oracle]ORA-01830: date format picture ends before converting entire input string ORA-06512:

issue?

Any input is highy appreciated.
Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Jun 04, 2013 8:15 pm

In short, make absolutely sure that your date picture matches the format of your data. And make sure, if you're using it, that the second and third arguments of the substr function are correct.

prasannakumarkk · Post by **prasannakumarkk** » Tue Jun 04, 2013 10:17 pm

Can you post some additional information?
You said your input is XML? What is the datatype and element you have given in column mapping in stored procedure stage.
How the XML is sent as input to stored procedure stage? Whether column value in dataset or XML output stage?

Above all this could be your problem ?

<DATE>2011-01-01-01:00</DATE>

This could have been parsed as entity reference something like this:

Code: Select all

< DATE > 2011-01-01-01:00 </DATE>

vinodkumards · Post by **vinodkumards** » Tue Jun 04, 2013 10:37 pm

storedProedure( in,out )

prasannakumarkk · Post by **prasannakumarkk** » Tue Jun 04, 2013 10:47 pm

Did you try making the same SP call with same parameters through anonymous block from sql plus/tools and succeeded?

vinodkumards · Post by **vinodkumards** » Wed Jun 05, 2013 6:42 am

blank

chulett · Post by **chulett** » Wed Jun 05, 2013 6:56 am

Your date mask matches the incoming string so to me that leaves your substring as suspect. You said:

vinodkumards wrote:Inside the SP,

W_Date :=to_date(substr(in_Rq,m,n),'yyyy-mm-dd')

where m,n are the xml tag positions

M would be the start position but N would be the string length, not the ending position.

prasannakumarkk · Post by **prasannakumarkk** » Wed Jun 05, 2013 9:29 pm

date inside the procedure will be used after removing xml tags

How are we achieving this? Please post on how you are assigning "in_Rq" variable

vinodkumards · Post by **vinodkumards** » Wed Jun 05, 2013 9:36 pm

If pass date as '2013-06-06' gives the same error.

vinodkumards · Post by **vinodkumards** » Thu Jun 06, 2013 1:44 pm

Any solution for this issue?

chulett · Post by **chulett** » Thu Jun 06, 2013 3:33 pm

Undoubtedly.

Your procedure code as written will ONLY work if the date is passed in as coded in the TO_DATE function call: YYYY-MM-DD. Are you certain it throws the ORA error when your XML element looks like "<DATE>2011-01-01</DATE>"? No extra spaces, just a 10 character date string between those tags?

vinodkumards · Post by **vinodkumards** » Thu Jun 06, 2013 11:31 pm

SQLSTATE = HY000, fNativeError = 0
date format picture ends before converting entire input string ORA-06512: at

ray.wurlod · Post by **ray.wurlod** » Fri Jun 07, 2013 12:55 am

Make absolutely sure that your date picture matches the format of your data. Completely.