| Author |
Message
|
| Marek |
 Posted: Wed Dec 20, 2006 9:36 am Post subject: Oracle Stored Procedure Ref Cursor Dynamic Result Sets |
 |
|
Apprentice
Joined: 30 Jun 2004
Posts: 32
Location: Edinburgh
|
I'm using WMB 6.0.0.1 and Oracle 9i.
I want to call an Oracle procedure and return a result set using a REF CURSOR as illustrated in the WMB 6 ESQL manual P.81 (ftp://ftp.software.ibm.com/software/integration/wbibrokers/docs/V6.0/messagebroker_ESQL.pdf).
Oracle Code:
| Code: |
-- Spec
TYPE t_cursor_variable IS REF CURSOR
PROCEDURE GetAllFunds1(p_fund_list OUT t_cursor_variable);
--Body
PROCEDURE GetAllFunds1(p_fund_list OUT t_cursor_variable)
IS
BEGIN
OPEN p_fund_list FOR
SELECT DISTINCT
f.fund_id AS fund_id,
...etc. |
WMB6 Code:
| Code: |
CREATE COMPUTE MODULE Dynamic_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
Call myProc1(Environment.Variables.TestResultSet[]);
RETURN TRUE;
END;
CREATE PROCEDURE myProc1()
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "sl666e.pkg_funds.GetAllFunds1";
END MODULE; |
Result:
BIP2322E: Database error: SQL State ''HY000''; Native Error Code '6550'; Error Text ''[DataDirect][ODBC Oracle driver][O
racle]ORA-06550: line 1, column 8: PLS-00306: wrong number or types of arguments in call to 'GETALLFUNDS1' ORA
-06550: line 1, column 8: PL/SQL: Statement ignored ''.
The error has the following diagnostic information: SQL State ''HY000'' SQL Native Error C
ode '6550' SQL Error Text ''[DataDirect][ODBC Oracle driver][Oracle]ORA-06550: line 1, column 8: PL
S-00306: wrong number or types of arguments in call to 'GETALLFUNDS1' ORA-06550: line 1, column 8: PL/SQL: Sta
tement ignored ''
This message may be accompanied by other messages describing the effect on the message broker itself. Use the
reason identified in this message with the accompanying messages to determine the cause of the error.
Previous postings on similar subjects are scarce and do not resolve this mystery.
Can anyone see any issues with this setup?
Many thanks. |
|
| Back to top |
|
 |
| JosephGramig |
| Posted: Wed Dec 20, 2006 12:04 pm Post subject: |
|
|
Grand Master
Joined: 09 Feb 2006
Posts: 1244
Location: Gold Coast of Florida, USA
|
Well, I use DB2 and here is my code:
DB2
| Code: |
CREATE PROCEDURE GRAMIG.TEST_PROC ( IN LastName CHARACTER(20),
IN FirstName CHARACTER(20) )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- LastName
-- FirstName
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT * FROM GRAMIG.TEST_TAB;
-- SELECT *
-- FROM GRAMIG.TEST_TAB AS TEST_TAB;
-- Cursor left open for client application
OPEN cursor1;
END P1 |
ESQL Define
| Code: |
CREATE PROCEDURE testProcedure(IN ln CHAR, IN fn CHAR)
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "TEST_PROC";
|
ESQL Call
| Code: |
| CALL testProcedure('GRAMIG','JOSEPH', Environment.ResultSet1[]); |
_________________
Joseph
Administrator - IBM WebSphere MQ (WMQ) V6.0, IBM WebSphere Message Broker (WMB) V6.1 & V6.0
Solution Designer - WMQ V6.0
Solution Developer - WMB V6.1 & V6.0, WMQ V5.3 |
|
| Back to top |
|
|
| Marek |
| Posted: Thu Dec 21, 2006 4:13 am Post subject: |
|
|
Apprentice
Joined: 30 Jun 2004
Posts: 32
Location: Edinburgh
|
Thanks for the DB2 example. I like the consistent use of 'DYNAMIC RESULT SETS' in the esql and DB2 procedure although I that's a 'convenience' to be expected when IBM are the vendor of both WMB and DB2.
Tried changing my cursor from:
| Code: |
TYPE t_cursor_variable IS REF CURSOR;
PROCEDURE GetAllFunds1(p_fund_list OUT t_cursor_variable) |
to
| Code: |
| PROCEDURE GetAllFunds1(p_fund_list OUT SYS_REFCURSOR) |
... but still getting the same error.
Does anyone have any other ideas? Thanks again.
Source: http://www.mqseries.net/phpBB2/viewtopic.php?t=32166&highlight=dynamic+result+sets |
|
| Back to top |
|
|
| mgk |
| Posted: Thu Dec 21, 2006 11:19 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
I believe there is a parameter on the ODBC driver to allow the procedure to return result sets, have you got this set?
Which platform is this?
_________________
MGK
The postings I make on this site are my own and don't necessarily represent IBM's positions, strategies or opinions. |
|
| Back to top |
|
|
| Marek |
| Posted: Fri Dec 22, 2006 2:22 am Post subject: |
|
|
Apprentice
Joined: 30 Jun 2004
Posts: 32
Location: Edinburgh
|
Many thanks for your reply:
Platform:
Oracle 9i DB on AIX 5.3
WMB 6 Broker on AIX 5.3
ODBC.ini:
[IN05]
Driver=/oem/wmb/syst/merant/lib/UKor820.so
Description=Oracle8
ServerName=IN05UNIT
EnableDescribeParam=1
OptimizePrepare=1
ProcedureRetResults=1
The ODBC.ini file had parm ProcedureRetResults=1 recently added after reading this recommendation in another post although this did not help.
The driver is the most up to date option provided with the WMB6 installation and works well in every other scenario. |
|
| Back to top |
|
|
| francoisvdm |
| Posted: Fri Dec 22, 2006 6:14 am Post subject: |
|
|
Partisan
Joined: 09 Aug 2001
Posts: 332
|
WOW--- I'm working on EXACTLY the same problem now. I made progress and I receive data back after I set the flag in the ODBC driver ProcedureRetResults=1
.
But, now I receive the SELECT statement in the stored procedure back... and not the data.
_________________
If you do not know the answer or you get the urge to answer with "RTFM" or "Search better in this forum", please refrain from doing so, just move on to the next question. Much appreciated.
Francois van der Merwe |
|
| Back to top |
|
|
| JosephGramig |
| Posted: Fri Dec 22, 2006 9:31 am Post subject: |
|
|
Grand Master
Joined: 09 Feb 2006
Posts: 1244
Location: Gold Coast of Florida, USA
|
Interesting,
Mine works great but I have this in my .odbc.ini file:
| Code: |
[apdb]
Driver=/opt/IBM/mqsi/6.0/merant/lib/UKor820.so
Description=DataDirect 5.0 Oracle
EnableDescribeParam=1
OptimizePrepare=1
ServerName=apdb
WorkArounds=536870912
ProcedureRetResults=1
|
Are you using the Merant drivers?
_________________
Joseph
Administrator - IBM WebSphere MQ (WMQ) V6.0, IBM WebSphere Message Broker (WMB) V6.1 & V6.0
Solution Designer - WMQ V6.0
Solution Developer - WMB V6.1 & V6.0, WMQ V5.3 |
|
| Back to top |
|
|
| francoisvdm |
| Posted: Wed Dec 27, 2006 3:24 am Post subject: |
|
|
Partisan
Joined: 09 Aug 2001
Posts: 332
|
I'm using the default Datadirect driver. Mine working now.... interpretation error between myself and Oracle stored procedure where they return Dynamic dataset.
_________________
If you do not know the answer or you get the urge to answer with "RTFM" or "Search better in this forum", please refrain from doing so, just move on to the next question. Much appreciated.
Francois van der Merwe |
|
| Back to top |
|
|
| Marek |
| Posted: Thu Dec 28, 2006 4:07 am Post subject: |
|
|
Apprentice
Joined: 30 Jun 2004
Posts: 32
Location: Edinburgh
|
Got this working now. The solution is:
Driver:
MQSeries DataDirect Technologies 5.0 32-BIT Oracle
Combined with:
ODBC parm:
ProcedureRetResults=1
Thanks to everyone who contributed to this post. |
|
| Back to top |
|
|
|
|
