Reference Cursor Gets Lost In Xmltype.createxml

I am calling a function that returns a reference cursor, and I am using XMLType.createxml to convert the results to XML, e.g. select XMLType.createxml(package_name.storedProcName('

Solution 1:

There seems to be a bug, you should open a service request to Oracle support. I'll post a test case that reproduces your finding in 9i and 11.2.0.3:

SQL>SHOWparameter open_cursors

NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------
open_cursors                         integer600SQL>CREATEOR REPLACE FUNCTION ret_cursor RETURN SYS_REFCURSOR IS2     l SYS_REFCURSOR;
  3BEGIN4OPEN l FOR5SELECT*FROM dual;
  6RETURN l;
  7END;
  8/Function created

XMLType will not close cursors correctly when called with the above function, whereas it works well with static SQL:

SQL>/* Works as expected with static cursor */2DECLARE3     l XMLTYPE;
  4BEGIN5FOR i IN1 .. 1e4 LOOP
  6SELECT xmltype.createXML(CURSOR(SELECT*FROM DUAL)) INTO l FROM dual;
  7END LOOP;
  8END;
  9/      

PL/SQLprocedure successfully completed

SQL>/* Fails with call to dynamic cursor */SQL>DECLARE2     l XMLTYPE;
  3BEGIN4FOR i IN1 .. 1e4 LOOP
  5SELECT xmltype.createXML(ret_cursor) INTO l FROM dual;
  6END LOOP;
  7END;
  8/DECLARE*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at "APPS.RET_CURSOR", line 4
ORA-06512: at line 5

You should be able to use a wrapper function to prevent the ORA-01000 from happening (tested on 9iR2, 11gR2):

SQL>CREATEOR REPLACE FUNCTION wrap_xml(p SYS_REFCURSOR) RETURN XMLTYPE IS2     l XMLTYPE;
  3BEGIN4     l := xmltype.CreateXML(p);
  5     IF p%ISOPEN THEN6CLOSE p;
  7END IF;
  8RETURN l;
  9END;
 10/Function created

SQL>DECLARE2     l XMLTYPE;
  3BEGIN4FOR i IN1 .. 1e4 LOOP
  5        l := wrap_xml(ret_cursor); -- a SELECT FROM dual will still fail here6-- on 9i but not on 11g 7END LOOP;
  8END;
  9/

PL/SQLprocedure successfully completed