| Author |
Message
|
| Dennis |
 Posted: Fri May 28, 2010 4:32 am Post subject: Calling an oracle function from WMB V6.1 |
 |
|
Apprentice
Joined: 04 May 2010
Posts: 29
|
I am calling an oracle funtion that returns a table of data. I have coed this just like calling a stored procedure. I get this error. I have ODBC 5.3 driver
[DataDirect][ODBC Oracle driver]Optional feature not implemented.
CALL dbFunc (planId,startDate,endDate) IN Database.{ProcessHandler_GetFundProposalStatus_SALESLOGIX_DSN}.{ProcessHandler_GetFundProposalStatus_SALESLOGIX_Schema} INTO envRef.ReturnValues;
CREATE PROCEDURE dbFunc(IN P1 CHARACTER, IN P2 CHARACTER, IN P3 CHARACTER)
RETURNS CHARACTER
LANGUAGE DATABASE EXTERNAL NAME ".GET_FUND_PROPOSAL_REQUESTS"; |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Fri May 28, 2010 5:43 am Post subject: Re: Calling an oracle function from WMB V6.1 |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| Dennis wrote: |
| [DataDirect][ODBC Oracle driver]Optional feature not implemented. |
This typically means the ODBC datasource is not configured quite right. You should compare the workarounds value with the documented version and make sure you have otherwise updated your odbc.ini/odbc32/odbc64 ini files with the DD 5.3 values. |
|
| Back to top |
|
|
| Dennis |
| Posted: Thu Jun 03, 2010 5:21 am Post subject: Calling an oracle function from WMB V6.1 |
|
|
Apprentice
Joined: 04 May 2010
Posts: 29
|
| I have set this at 536870912. The database function I am calling is returning a table with rows of data. Is there any special way I need to DECLARE my INTO variable on the CALL statement? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 03, 2010 6:01 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| You can try envRef.ReturnValues[] |
|
| Back to top |
|
|
| Dennis |
| Posted: Thu Jun 03, 2010 6:21 am Post subject: Calling an oracle function from WMB V6.1 |
|
|
Apprentice
Joined: 04 May 2010
Posts: 29
|
That does not even deploy. Get the following error. One thing I did find out is the database that the stored function is on is Oracle 8.1.7.4. Is that support with WMB V6.1 and the DataDirect driver 5.3 I am using????
BIP2496E: (com.hli.icc.retirementplan.rpgfundproposalservice.v100.process.GetFundProposalStatus_DAO.Main, 101.190) : Illegal data type for target. A non-list field reference is required.
The expression supplying the target must evaluate to a value of a suitable type. The given expression cannot possibly do so.
Correct the syntax of your ESQL expression in node 'com.hli.icc.retirementplan.rpgfundproposalservice.v100.process.GetFundProposalStatus_DAO.Main', around line and column '101.190', then redeploy the message flow. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Jun 03, 2010 9:06 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Okay, so it wants a single value rather than a list reference.
I don't believe that Oracle 8 is still supported with Broker v6.1, but I haven't double-checked the supported databases page....
The workarounds value in the ODBC connection may not be the correct workarounds value for Oracle 8 either, but I really wouldn't know what the correct one was. Ideally that would also be in the InfoCenter.
I also recall some conversation about multi-valued returns from functions specifically related to Oracle. You might do some searching here -particular for comments from mgk. |
|
| Back to top |
|
|
| Dennis |
| Posted: Thu Jun 03, 2010 9:10 am Post subject: Calling an oracle function from WMB V6.1 |
|
|
Apprentice
Joined: 04 May 2010
Posts: 29
|
| Thanks for the info...... |
|
| Back to top |
|
|
|
|
