| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Evaluating the Return Value from External Stored Procedure |
« View previous topic :: View next topic » |
| Author |
Message
|
| catwood2 |
 Posted: Tue May 31, 2005 6:29 am Post subject: Evaluating the Return Value from External Stored Procedure |
 |
|
Centurion
Joined: 17 May 2002
Posts: 108
|
I'm new to and experiencing a little confusion around executing external stored procedures. Thoughts appreciated.
WBIMB 5 CSD 4
Oracle 10.1.0.3.1
I have been asked to execute an Oracle stored procedure and evaluate the Return Value and then act accordingly. I have been advised there are no Out or In Out parameters to evaluate....only the 'Return Value' is output. It is unclear to me how to evaluate that after the stored procedure is executed. I have read thru the threads here and the manual - but, I'm not quite seeing how I get to the output.
So, in code below, I find out if the user exists (Return Value is not null) and then go off and execute update procedure......
| Code: |
IF action = 'Insert' OR 'update' THEN
Call usersearch (username, OIDPort, OIDHost, OIDPass);
Create Procedure usersearch (
IN parm1 Character,
IN parm2 Integer,
IN parm3 Character,
IN parm4 Character
) EXTERNAL NAME "portal.LDAP_CUSTOM_INTERFACE2.usersearch";
....execute update user proc |
[/code]
thx |
|
| Back to top |
|
 |
| mgk |
| Posted: Tue May 31, 2005 7:25 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
If you actually mean a return value, then this is really a Stored Function according to Oracle, and this is not currently supported. You can use an OUT parameter to get back a value from the procedure, but this means you have to change the procedure. However, if you mean the SQL_CODE from the procedure, then read the docs for how to handle database errors
_________________
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 |
|
|
| catwood2 |
| Posted: Tue May 31, 2005 8:47 am Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
mgk,
Thanks! I did actually mean a return value. Is the fact that this is not supported just implied by the manual writeup in 'Invoking Stored Procedures' section?
thanks again. |
|
| Back to top |
|
|
| JT |
| Posted: Tue May 31, 2005 9:14 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Although it may not be supported, obtaining a return value (i.e. a result-set from a SELECT call) from an Oracle stored function is possible. |
|
| Back to top |
|
|
| catwood2 |
| Posted: Tue May 31, 2005 9:29 am Post subject: |
|
|
Centurion
Joined: 17 May 2002
Posts: 108
|
JT,
Pointer to where I might found out how to do this? Passthru?
thx |
|
| Back to top |
|
|
| JT |
| Posted: Tue May 31, 2005 10:48 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
It's been sometime since we implemented this design, so I really don't have any sources to refer you to, but here's how we did it.
The requirement was to invoke existing Oracle stored functions from our WBI-tier, without modifying them. A typical application stored function looked like this:
| Code: |
function getAllFunds return psa_refcur_pkg.refcur_t is
ResultSet psa_refcur_pkg.refcur_t;
BEGIN
OPEN ResultSet FOR
select * from ann_fund order by fundname;
return ResultSet;
end getAllFunds; |
Since WBI didn't accept the return of reference cursors, we created new stored functions to invoke the application stored functions and convert the 'returned' result-set, from a reference cursor to a string that was XML-formatted. It looks like this:
| Code: |
return clob is
l_xml clob;
qryCtx DBMS_XMLGEN.ctxHandle;
BEGIN
qryCtx := dbms_xmlgen.newContext('select PRODUCTSA.psa_productcentral_pkg.getAllFunds() 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 getAllFunds_XML; |
Then from the message flow we invoke the new stored functions, assign the result-set to an Environment variable, and re-constitute it using the XML parser like so:
| Code: |
SET Environment.Variables.XML.Temp.Response[] =
PASSTHRU('SELECT PRODUCTSA.getAllFunds_XML() DATA FROM DUAL');
DECLARE tempCharString CHARACTER CAST(Environment.Variables.XML.Temp.Response.DATA AS CHARACTER CCSID InputRoot.MQMD.CodedCharSetId);
DECLARE tempBlobString BLOB CAST(tempCharString as BLOB CCSID InputRoot.MQMD.CodedCharSetId);
CREATE FIRSTCHILD OF Environment.Variables.Response.Data Domain('XML') PARSE(tempBlobString);
|
That's all there is to it. |
|
| 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
|
|
|
|
