| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Oracle Stored Procedure - REF CURSOR |
« View previous topic :: View next topic » |
| Author |
Message
|
| fcotait |
 Posted: Thu Jul 15, 2004 6:56 am Post subject: Oracle Stored Procedure - REF CURSOR |
 |
|
Acolyte
Joined: 28 Feb 2002
Posts: 63
Location: Sao Paulo - Brazil
|
Hi all,
I'm trying to call a Oracle Stored Procedure with REF CURSOR parameter.
How can I pass a REF CURSOR to Oracle through the WBIMB compute node ?
My WBIMB version is 5 with CSD 3 and the Oracle9i
The SP DESC is:
| Code: |
SQL> desc SICAD.P_SICCABINIF;
PROCEDURE SICAD.P_SICCABINIF
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
P_CURSORBINIF REF CURSOR IN/OUT
RECORD IN/OUT
BIN VARCHAR2(8) IN/OUT
ENTLRON VARCHAR2(2) IN/OUT
P_CODIF VARCHAR2 IN
|
and my ESQL code:
| Code: |
CREATE COMPUTE MODULE Ate_01_Compute
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
CALL CopyEntireMessage();
DECLARE P_CursorBinIF CHAR;
DECLARE P_CodIF CHAR;
SET P_CodIF = InputRoot.XML.Dados.Bin;
SET P_CursorBinIF = ' ';
CALL StrProced(P_CursorBinIF, P_CodIF);
RETURN TRUE;
END;
CREATE PROCEDURE StrProced (
INOUT Parm1 CHAR,
IN Parm2 CHAR
) EXTERNAL NAME "SICAD.P_SICCABINIF";
CREATE PROCEDURE CopyMessageHeaders() BEGIN
DECLARE I INTEGER 1;
DECLARE J INTEGER CARDINALITY(InputRoot.*[]);
WHILE I < J DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I = I + 1;
END WHILE;
END;
CREATE PROCEDURE CopyEntireMessage() BEGIN
SET OutputRoot = InputRoot;
END;
END MODULE; |
I already changed the variable value P_CursorBinIF for many types, how: NULL, '', ' ', etc...
Any Idea ?
Thanks
_________________
Filipe Cotait
IBM Certified System Administrator - WebSphere MQ
IBM Certified Specialist - MQSeries, WebSphere MQ Integrator |
|
| Back to top |
|
 |
| JT |
| Posted: Wed Jul 21, 2004 8:01 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
fcotait,
Is it possible to preface the call to the SICAD.P_SICCABINIF stored-procedure with a call to another stored-procedure that 'converts' (as you can see I'm database-lliterate) the in-bound P_CursorBinIF (Parm1) to the ref_cursor?
Earlier this year we had a similar requirement, but in reverse. We needed to invoke legacy Oracle stored-functions that returned the resultset as a ref_cursor. Since WBI (Merant driver) didn't support this data type, we prefaced the call to the stored function with another function. It's purpose was to 'convert' the ref_cursor to a CLOB using the DBMS_XMLGEN function.
The legacy stored-function looked something like this:
| Code: |
function getProductFunds(pProdId in number) return psa_refcur_pkg.refcur_t is
ResultSet psa_refcur_pkg.refcur_t;
begin
OPEN ResultSet FOR
select pf.*, f.* from productfund pf, fund f
where
f.TYPECODE = 'V' and
(f.TERMINATIONDATE IS NULL or f.TERMINATIONDATE >= sysdate) and
(f.CLOSEDTONEWSALESDATE IS NULL or pf.TERMINATIONDATE >= sysdate)
and (pf.TERMINATIONDATE IS NULL or pf.TERMINATIONDATE >= sysdate) and
pf.FUNDCODE=f.FUNDCODE and pf.PRODUCTID=pProdId
order by pf.TERMINATIONDATE desc, f.TERMINATIONDATE desc, f.FUNDNAME;
return ResultSet;
end getProductFunds; |
We created this function to call the one above:
| Code: |
function getProductFunds_XML(pProdId in number) return clob is
l_xml clob;
qryCtx DBMS_XMLGEN.ctxHandle;
begin
qryCtx := dbms_xmlgen.newContext('select PRODUCTSA.productcentral_pkg.getProductFunds(' || '''' || pProdId || '''' || ') RESULTS from dual');
l_xml := DBMS_XMLGEN.getXML(qryCtx);
dbms_xmlgen.closeContext(qryCtx);
return l_xml;
exception
when others then
dbms_xmlgen.closeContext(qryCtx);
raise;
end getProductFunds_XML; |
Hope this gives you some ideas/possibilities? |
|
| 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
|
|
|
|
