| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Milliseconds getting dropped when calling Oracle Stored Proc |
« View previous topic :: View next topic » |
| Author |
Message
|
| dilse |
 Posted: Tue Dec 04, 2012 1:02 pm Post subject: Milliseconds getting dropped when calling Oracle Stored Proc |
 |
|
Master
Joined: 24 Jun 2004
Posts: 270
|
Hi there..
Broker version:8.0.0.1
I am trying to call a Stored procedure using ESQL. One of the input parameters is the TIMESTAMP and it is defined as TIMESTAMP(6) in Oracle table as well. When I took the trace, it showing that the TIMESTAMP field was being sent properly with the value being shown as following in the trace.
| Code: |
| ''TIMESTAMP '2012-01-15 12:34:04.134''' |
But when the SP is called and updated the table, it is missing the milliseconds details. The stored procedure also has some OUT parameters so I cannot use PASSTHRU statement. But based on the documentation the ESQL TIMESTAMP can only be a DATE in Oracle.
How do I update the table with having milliseconds.
Any ideas are appreciated!!!
_________________
DilSe..
Take life as it comes.. |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Tue Dec 04, 2012 1:09 pm Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
|
| Back to top |
|
|
| kash3338 |
| Posted: Tue Dec 04, 2012 6:39 pm Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
The TIMESTAMP data type in Oracle stores only the year, month, and day of the DATE data type, plus hour, minute, and second values.
I guess it does not hold the milliseconds details in it. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Wed Dec 05, 2012 12:41 am Post subject: Re: Milliseconds getting dropped when calling Oracle Stored |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| dilse wrote: |
| But based on the documentation the ESQL TIMESTAMP can only be a DATE in Oracle. |
The Oracle DATE datatype only supports integer values for year, month, day, hour, minute, and second, so it's not possible to preserve the milliseconds when converting:
ESQL TIMESTAMP --> Oracle DATE --> Oracle TIMESTAMP.
| dilse wrote: |
| How do I update the table with having milliseconds. |
Don't pass an ESQL TIMESTAMP to the stored procedure. Try converting it to ESQL CHARACTER first, and pass that instead. The stored procedure can then either implicitly or explicitly convert from one of the Oracle character datatypes to Oracle TIMESTAMP. |
|
| Back to top |
|
|
| dilse |
| Posted: Wed Dec 05, 2012 12:26 pm Post subject: |
|
|
Master
Joined: 24 Jun 2004
Posts: 270
|
Thanks for all your inputs.
| Quote: |
| The TIMESTAMP data type in Oracle stores only the year, month, and day of the DATE data type, plus hour, minute, and second values. |
Thats incorrect. Oracle TIMESTAMP can hold the date, time including micro seconds.
| Quote: |
Don't pass an ESQL TIMESTAMP to the stored procedure. Try converting it to ESQL CHARACTER first, and pass that instead. The stored procedure can then either implicitly or explicitly convert from one of the Oracle character datatypes to Oracle TIMESTAMP. |
Thanks I tried this but it was throwing an error as the value that the SP expecting is TIMESTAMP.
Instead I had requested the DBA to change the parameter to change the field from TIMESTAMP to CHAR on the SP signature to workaround the issue and later they will convert it to a TIMESTAMP when updating a table. This as you all know works but I was trying to see if we can do it without doing this.
Again appreciate all your inputs!!
_________________
DilSe..
Take life as it comes.. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
