| Author |
Message
|
| Jason_Mannering |
 Posted: Mon Mar 20, 2006 6:53 am Post subject: Returning result sets from Oracle Database Stored Procedure |
 |
|
Newbie
Joined: 24 Mar 2005
Posts: 8
Location: London
|
Please can someone provide some information on how I might return return a result set from an Oracle Stored Procedure to Message Broker?
We have tried the following options which do not seem to work:
1. Used a "Call" to the Stored Procedure which uses an Oracle refCursor as an output parameter. Seems Message Broker does not support refCursor.
2. Used "Passthru" to the Stored Procedure which uses an Oracle refCursor as an output parameter, although did not expect this to work as "Passthru does not support output parameters" |
|
| Back to top |
|
 |
| msukup |
| Posted: Mon Mar 20, 2006 7:04 am Post subject: Re: Returning result sets from Oracle Database Stored Proced |
|
|
Acolyte
Joined: 11 Feb 2002
Posts: 56
|
I have posted in the instructions given to me for this problem:
Using REF CURSORS with Oracle Stored Procedures via PASSTHRU
Oracle and ODBC Setup
To use Oracle REF CURSORS as parameters to stored procedures you must have the ProcedureRetResults=1 parameter set for the Oracle Data Source. This enables the ODBC driver to return result sets from stored procedures.
Also, it is important that the stored procedure is defined in a particular format. First a package must be created to define the cursor that is used in the procedure, then the procedure is created to use the new cursor.
For example,
CREATE TABLE EMP(empno NUMBER(3), firstname VARCHAR2(20), surname VARCHAR2(20), age NUMBER(3));
INSERT INTO EMP(1, 'George', 'Green', 20);
INSERT INTO EMP(2, 'Mary', 'Brown', 30);
INSERT INTO EMP(3, 'Graham', 'Black', 40);
INSERT INTO EMP(4, 'Sue', 'Blue', 50);
INSERT INTO EMP(5, 'John', 'Orange', 60);
CREATE OR REPLACE PACKAGE emp_types AS
CURSOR G1 IS SELECT firstname FROM EMP;
TYPE empcursor is REF CURSOR RETURN G1%rowtype;
END emp_types;
CREATE OR REPLACE PROCEDURE GetEmpCursor(theemp IN VARCHAR2, rspCursor IN OUT emp_types.empcursor) AS
BEGIN
OPEN rspCursor FOR SELECT firstname FROM EMP WHERE firstname LIKE theemp || '%';
END GetEmpCursor
After creating the above the stored procedure can then be tested from SQL*Plus, as follows:
VARIABLE rc REF CURSOR
CALL GetEmpCursor('G', :rc);
PRINT :rc
This should return all the 'firstnames' from the EMP table that begin with 'G' i.e. 'George' and 'Graham'.
Broker ESQL
To use the stored procedure from message broker the following ESQL can be used:
SET OutputRoot = InputRoot;
SET OutputRoot.XML.Message.Result.Data[] = PASSTHRU('{CALL GetEmpCursor(?)}', InputRoot.XML.Message.SearchName);
Note: Only one parameter is provided in the call to the stored procedure even though the stored procedure is defined with two parameters. This is because the driver resolves the REF CURSOR parameter so it is not necessary to provide the REF CURSOR as a parameter.
Running the above EQSL with an XML input message of <Message><SearchName>G</SearchName></Message> would produce the following output message:
<Message><SearchName>G</SearchName><Result><Data><FIRSTNAME>George></FIRSTNAME></Data><Data><FIRSTNAME>Graham</FIRSTNAME></Data></Result></Message>
Known Issues
The MQSI_EMPTY_DB_CACHE=Y environment variable must be set in the broker's environment. If this is not set then the first message processes successfully but subsequent messages fail with the following error: |
|
| Back to top |
|
|
| mgk |
| Posted: Mon Mar 20, 2006 10:36 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
Which version of Message Broker are you using?
The steps posted by msukup are correct for V5.
However, for V6, support for result sets (single or multiple) has been improved. In V6, you can use CALL and CREATE PROCEDURE (so you can use IN/OUT/INOUT scalar parameters in addition to returning result sets).
You can even RETURN a scalar value as well (which means CREATE FUNCTION on Oracle). In addition, the environment variable is not needed.
_________________
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 |
|
|
| waugh |
| Posted: Fri May 05, 2006 11:48 am Post subject: SQL server store procedure |
|
|
Master
Joined: 19 Feb 2004
Posts: 225
|
I am using v6 broker.
I tried this SQL server stored procedure. No parameters required.
with
CREATE PROCEDURE storeproctest() EXTERNAL NAME "dbo.GetTransactionRecords" ;
then
CALL storeproctest(); in Main(). it was executed successfully.
Now, i want to write the result set of the stored procedure to XML Tree.
Ex: OutputRoot.XML.ResultSet[] =
How do we do this?
Thanks in advance. |
|
| Back to top |
|
|
| mgk |
| Posted: Sun May 07, 2006 8:48 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Hi,
Look up the DYNAMIC RESULT SETS clause of the CREATE PROCEDURE statement in the V6 docs. This will explain how to do 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 |
|
|
| waugh |
| Posted: Mon May 08, 2006 11:00 am Post subject: zero records |
|
|
Master
Joined: 19 Feb 2004
Posts: 225
|
Thanks gmk,
i changed procedure definition to
CREATE PROCEDURE storeproctest() DYNAMIC RESULT SETS 1 EXTERNAL NAME "dbo.GetTransactionRecords" ;
then procedure call to
CALL storeproctest(Environment.ResultSet[]);
then i have this statement
SET OutputRoot.XML.totalrecords = CARDINALITY(Environment.Resultset[]);
this gives me the output as
<totalrecords>0</totalrecords>, no errors in broker logs or syslogs
I am seeing 12 records if i run the procedure directly on database.
Did i miss something? |
|
| Back to top |
|
|
| mgk |
| Posted: Tue May 09, 2006 12:24 am Post subject: |
|
|
Padawan
Joined: 31 Jul 2003
Posts: 1642
|
Can you post your procedure definition, and the output from a Tracenode showing the Environment tree after you have made the call to the procedure pelase.
_________________
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 |
|
|
| waugh |
| Posted: Tue May 09, 2006 6:00 am Post subject: trace output |
|
|
Master
Joined: 19 Feb 2004
Posts: 225
|
thanks for your help.
After tracing i noticed there is a spelling mistake in the below statement.
SET OutputRoot.XML.totalrecords = CARDINALITY(Environment.ResultSet[]);
Last edited by waugh on Tue May 09, 2006 6:05 am; edited 1 time in total |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue May 09, 2006 6:05 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Did you mean to write "CARDINALITY(Environment.Resultset[]);", or did you mean to write "CARDINALITY(Environment.ResultSet[]);"?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| waugh |
| Posted: Tue May 09, 2006 9:43 am Post subject: |
|
|
Master
Joined: 19 Feb 2004
Posts: 225
|
| I meant to write CARDINALITY(Environment.ResultSet[]); had lower case s there. |
|
| Back to top |
|
|
|
|
