| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Inserting [b]DATE[/b] field into Oracle 8.1 using WBIMB |
« View previous topic :: View next topic » |
| Author |
Message
|
| biswagourab |
 Posted: Wed Jun 21, 2006 11:13 am Post subject: Inserting [b]DATE[/b] field into Oracle 8.1 using WBIMB |
 |
|
Apprentice
Joined: 31 May 2005
Posts: 47
Location: London
|
Hi All,
I am trying to insert the date field into the Oracle DB using the compute node in WBIMB 6.0.
I am not able to do so and am getting this error
The correlation name ''to_date('20060620','YYYYMMDD')'' is not valid. Those in scope are: 'Environment, InputLocalEnvironment, OutputLocalEnvironment, InputRoot, InputBody, InputProperties, OutputRoot, InputExceptionList, OutputExceptionList, InputDestinationList, OutputDestinationList'.
It says the to_date function is not defined.
The command I am using is....
PASSTHRU('INSERT INTO Database.GEC_HFS_BBC.TBL_X_CUSTOMER_INFO(CUSTOMER_ID,PA_SSO_ID,LAST_EXTRACTED_DATE)
VALUES(?,?,?)', SUBSTRING(InputRoot.BLOB.BLOB FROM 1 FOR  ,SUBSTRING(InputRoot.BLOB.BLOB FROM 9 FOR 10),to_date('20060620','YYYYMMDD') );
Please help me in getting to the root of this problem. I can insert using TOAD so the to_date is recognised by the Oracle DB.
I can render any other info if you want.
Thanks
Biswa |
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Wed Jun 21, 2006 2:37 pm Post subject: Re: Inserting [b]DATE[/b] field into Oracle 8.1 using WBIMB |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| biswagourab wrote: |
PASSTHRU('INSERT INTO Database.GEC_HFS_BBC.TBL_X_CUSTOMER_INFO(CUSTOMER_ID,PA_SSO_ID,LAST_EXTRACTED_DATE)
VALUES(?,?,to_date(?,'YYYYMMDD'))', SUBSTRING(InputRoot.BLOB.BLOB FROM 1 FOR ,SUBSTRING(InputRoot.BLOB.BLOB FROM 9 FOR 10),'20060620' ); |
If you had sat down and really thought about what you were actually doing, you would have figured this out yourself.
Please remember what PASSTHRU does.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| biswagourab |
| Posted: Wed Jun 21, 2006 5:14 pm Post subject: |
|
|
Apprentice
Joined: 31 May 2005
Posts: 47
Location: London
|
Hi Jefflowrey,
You would be glad to know that I was going on the right path. I was trying VALUES(?,?,to_date('20060620','YYYYMMDD'))' hehe
But now the bad news. I tried the way you guided me to,but when i save the code, I get an error message saying that
Syntax Error : Valid options are , || / + = - * AND BETWEEN IS LIKE MINUTE MONTH OR SECOND YEAR
This error is at the position where i have marked the *
VALUES(?,?,to_date(?, * 'YYYYMMDD'))'
I dont seem to understand why I am getting this error, which actually I should not get since I am using the PASSTHRU.
The code looks exactly as you have given it to me.
Thanks
Biswa |
|
| Back to top |
|
|
| biswagourab |
| Posted: Thu Jun 22, 2006 4:44 pm Post subject: Inserting DATE field into Oracle 8.1 using WBIMB (solved) |
|
|
Apprentice
Joined: 31 May 2005
Posts: 47
Location: London
|
Hi
the problem was finally solved when I used this syntax
PASSTHRU('INSERT INTO Database.GEC_HFS_BBC.TBL_X_CUSTOMER_INFO(CUSTOMER_ID,PA_SSO_ID,LAST_EXTRACTED_DATE)
VALUES(?,?,to_date(?))', SUBSTRING(InputRoot.BLOB.BLOB FROM 1 FOR ,SUBSTRING(InputRoot.BLOB.BLOB FROM 9 FOR 10),'20060620' );
Somehow the apostophe was not getting through.
But now it is working fine.
Regards |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Jun 22, 2006 5:40 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
I thought you had to be at least at Oracle 9 with broker 6?
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| biswagourab |
| Posted: Thu Jun 22, 2006 8:01 pm Post subject: |
|
|
Apprentice
Joined: 31 May 2005
Posts: 47
Location: London
|
Hi fjb_saper
I have an Oracle 9.2 client on my system. I am accessing a remote Oracle DB 8.1 on Linux server using ODBC.
Hope this info helps you.
Thanks |
|
| 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
|
|
|
|
