Page 1 of 1
issue with Date format in SP
Posted: Tue Jun 04, 2013 7:36 pm
by vinodkumards
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
Posted: Tue Jun 04, 2013 8:15 pm
by ray.wurlod
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.
Posted: Tue Jun 04, 2013 10:17 pm
by prasannakumarkk
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:
Posted: Tue Jun 04, 2013 10:37 pm
by vinodkumards
storedProedure( in,out )
Posted: Tue Jun 04, 2013 10:47 pm
by prasannakumarkk
Did you try making the same SP call with same parameters through anonymous block from sql plus/tools and succeeded?
Posted: Wed Jun 05, 2013 6:42 am
by vinodkumards
blank
Posted: Wed Jun 05, 2013 6:56 am
by chulett
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.
Posted: Wed Jun 05, 2013 9:29 pm
by prasannakumarkk
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
Posted: Wed Jun 05, 2013 9:36 pm
by vinodkumards
If pass date as '2013-06-06' gives the same error.
Posted: Thu Jun 06, 2013 1:44 pm
by vinodkumards
Any solution for this issue?
Posted: Thu Jun 06, 2013 3:33 pm
by chulett
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?
Posted: Thu Jun 06, 2013 11:31 pm
by vinodkumards
SQLSTATE = HY000, fNativeError = 0
date format picture ends before converting entire input string ORA-06512: at
Posted: Fri Jun 07, 2013 12:55 am
by ray.wurlod
Make absolutely sure that your date picture matches the format of your data. Completely.