MQSeries.net :: View topic - Oracle functions and open cursors

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Oracle functions and open cursors

Oracle functions and open cursors

« View previous topic :: View next topic »

Author

Message

Posted: Mon Mar 01, 2004 3:16 pm Post subject: Oracle functions and open cursors

Padawan

Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.

Can anyone provide some insight into open cursors and how they are closed when a user database is accessed through
a WMQIMB v5.0.2 message flow. We're encountering an issue with cursors left open, albeit in an inactive state, eventually
resulting in an "ORA-01000 maximum open cursors exceeded" error.

Our environment is: WMQIMB v5.0.2 using the Merant UKor818 driver to access Oracle 9.0.1 databases from brokers
running on a Solaris 2.8 platform

The following ESQL statement invokes an Oracle function that in turn invokes an Oracle packaged function:

Code:

SET OutputLocalEnvironment.Variables.XML.TlaMessage.Response[] =
passthru('select PRODUCTSA.getProductFunds_XML() DATA FROM DUAL');

The SQL code for the PRODUCTSA.getProductFunds_XML function is:

Code:

(pProdId in number) return clob is
   l_xml clob;
   qryCtx DBMS_XMLGEN.ctxHandle;
begin
   qryCtx := dbms_xmlgen.newContext('select PRODUCTSA.psa_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;

The purpose of the PRODUCTSA.getProductFunds_XML function is to convert the result-set (a reference cursor) returned by
the packaged function PRODUCTSA.psa_productcentral_pkg.getProductFunds into a CLOB, structured as XML. The reasoning
for this scenario is two-fold:

Quote:

1. The target packaged function cannot be modified to return a clob and cannot be replicated
(cultural issue not technical, the application does not want to maintain multiple versions)
2. WMQIMB does not support reference pointers (is this true ?)

The PRODUCTSA.psa_productcentral_pkg.getProductFunds function looks like this:

Code:

function getProductFunds(pProdId in number) return psa_refcur_pkg.refcur_t;

and the body of this function is:

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;

Increasing the parameter that controls the maximum number of open cursors is not an option as that value would then be
directly dependent upon the volume of messages, an increase in messages would necessitate and increase in the parameter.
Another Oracle parameter CLOSE_CACHED_OPEN_CURSORS has also been ruled out as it is no longer supported for Oracle 9i
databases.

Any suggestions or thoughts on the subject are appreciated.

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Oracle functions and open cursors

Jump to:

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

Protected by Anti-Spam ACP