issue with Date format in SP
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 8
- Joined: Tue Aug 24, 2010 7:52 pm
- Location: nj
issue with Date format in SP
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
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
Last edited by vinodkumards on Fri Jun 07, 2013 8:52 am, edited 2 times in total.
HI i AM A DATA STAGE CONSULTANT.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India
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:
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>
Thanks,
Prasanna
Prasanna
-
- Premium Member
- Posts: 8
- Joined: Tue Aug 24, 2010 7:52 pm
- Location: nj
storedProedure( in,out )
Last edited by vinodkumards on Thu Jun 13, 2013 5:00 pm, edited 1 time in total.
HI i AM A DATA STAGE CONSULTANT.
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India
-
- Premium Member
- Posts: 8
- Joined: Tue Aug 24, 2010 7:52 pm
- Location: nj
blank
Last edited by vinodkumards on Thu Jun 13, 2013 4:58 pm, edited 1 time in total.
HI i AM A DATA STAGE CONSULTANT.
Your date mask matches the incoming string so to me that leaves your substring as suspect. You said:
M would be the start position but N would be the string length, not the ending position.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
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 117
- Joined: Wed Feb 06, 2013 9:24 am
- Location: Chennai,TN, India
-
- Premium Member
- Posts: 8
- Joined: Tue Aug 24, 2010 7:52 pm
- Location: nj
If pass date as '2013-06-06' gives the same error.
Last edited by vinodkumards on Thu Jun 13, 2013 4:59 pm, edited 2 times in total.
HI i AM A DATA STAGE CONSULTANT.
-
- Premium Member
- Posts: 8
- Joined: Tue Aug 24, 2010 7:52 pm
- Location: nj
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 8
- Joined: Tue Aug 24, 2010 7:52 pm
- Location: nj
SQLSTATE = HY000, fNativeError = 0
date format picture ends before converting entire input string ORA-06512: at
date format picture ends before converting entire input string ORA-06512: at
Last edited by vinodkumards on Fri Jun 07, 2013 8:46 am, edited 1 time in total.
HI i AM A DATA STAGE CONSULTANT.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: