| Author |
Message
|
| mzm.shan |
 Posted: Thu Sep 26, 2013 9:34 pm Post subject: [ODBC Oracle Wire Protocol driver]String data, right truncat |
 |
|
Apprentice
Joined: 25 Aug 2013
Posts: 39
|
ERROR:
[ODBC Oracle Wire Protocol driver]String data, right truncated. Error in parameter 1.
Following is the TOAD output of an ORACLE DATE field.
TL_SYS_TIME
9/24/2013 9:19:05 PM
9/25/2013 11:10:00 AM
9/25/2013 11:27:06 AM
9/25/2013 11:25:30 AM
9/24/2013 11:01:04 PM
i have esql written with the following code that gives me the error..
select t.tl_sys_time FROM Database.iris.tlog_view as t WHERE t.tl_sys_time >= dateExe
where... "dateExe" is 2013-09-27 10:29:18.315 by
SET dateExe = (CURRENT_TIMESTAMP - CAST('15' AS INTERVAL SECOND)); |
|
| Back to top |
|
 |
| dogorsy |
| Posted: Thu Sep 26, 2013 9:46 pm Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
you have not specified what data type dateExe is.
| Code: |
where... "dateExe" is 2013-09-27 10:29:18.315 by
SET dateExe = (CURRENT_TIMESTAMP - CAST('15' AS INTERVAL SECOND)); |
As in a previous post, you need to read about data types and the CAST function.
Please read "Using numeric operators with datetime values" in the Infocenter. It contains examples |
|
| Back to top |
|
|
| mzm.shan |
| Posted: Thu Sep 26, 2013 11:40 pm Post subject: |
|
|
Apprentice
Joined: 25 Aug 2013
Posts: 39
|
its
DECLARE dateExe TIMESTAMP; |
|
| Back to top |
|
|
| dogorsy |
| Posted: Thu Sep 26, 2013 11:44 pm Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| mzm.shan wrote: |
its
DECLARE dateExe TIMESTAMP; |
in that case, why you say its value is "2013-09-27 10:29:18.315" ? it probably is " TIMESTAMP '2013-09-27 10:29:18.315' "
Have you run a user trace to see what value is being assigned to it ?
You need to learn to debug your own code. Try a few things. And run user trace to understand what is going on.
For example,
1- remove the WHERE clause from your select. Is that working ?
2- set dateExe = CURRENT_TIMESTAMP and select where all the dates are before that timestamp. Is that working ?. etc
The user trace will give you a lot of useful information. Read it and learn.
you will see in the trace that the CAST is unnecessary, you can code directly
| Code: |
SET dateExe = CURRENT_TIMESTAMP - INTERVAL '15' SECOND;
|
|
|
| Back to top |
|
|
| mzm.shan |
| Posted: Fri Sep 27, 2013 1:33 am Post subject: |
|
|
Apprentice
Joined: 25 Aug 2013
Posts: 39
|
i am watching this value in debug mode in Toolkit.
The query is correct beacue it runs fine with other field filters..
I tried the following as well but didnt work and gave same error.
do i have to cast it? if yes then what should be the FORMAT patteren that works with this particular oracle date field.
SET OutputRoot.XMLNSC.Data.abcd[] = (select t.tl_sys_time
FROM Database.iris.tlog_view as t
WHERE t.tl_sys_time = CURRENT_TIMESTAMP
);
i am sory if it is a dump question as i am a new bee in WMB and esql.  |
|
| Back to top |
|
|
| dogorsy |
| Posted: Fri Sep 27, 2013 2:54 am Post subject: |
|
|
Knight
Joined: 13 Mar 2013
Posts: 553
Location: Home Office
|
| mzm.shan wrote: |
The query is correct beacue it runs fine with other field filters..
|
Raise a PMR with IBM then. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Fri Sep 27, 2013 7:29 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
It would help to see the full error message, including the BIP number.
It would help to confirm that you are using the DataDirect ODBC Driver for Oracle and not using an Oracle native ODBC driver. |
|
| Back to top |
|
|
|
|
