SQL & PL/SQL

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production on Solaris

I have a procedure calling another procedure with one of the parameters being an IN OUT NOCOPY CLOB.

I create the temporary CLOB in proc_A, do the call to proc_B and then free the temporary again.
In proc_B I create a REFCURSOR, and use that with dbms_xmlgen to create XML.

So the code basically looks like

CREATE OR REPLACE PROCEDURE client_xml( p_client_id IN            NUMBER
                                       ,p_clob      IN OUT NOCOPY CLOB   ) AS
   v_rc         SYS_REFCURSOR;
   v_queryCtx   dbms_xmlquery.ctxType;
   
BEGIN
   OPEN c_rc FOR
      SELECT col1
            ,col2
            ,col3
        FROM clients
       WHERE client_id = p_client_id;

   v_queryCtx := dbms_xmlgen.newContext(v_rc);
   p_clob     := dbms_xmlgen.getXML(v_queryCtx, 0);
 
END;

-------------------------------------


CREATE OR REPLACE PROCEDURE my_proc AS
   v_clob       CLOB;
   v_client_id  NUMBER;
BEGIN
   v_client_id := 123456;

   dbms_lob.createTemporary(v_clob, TRUE, dbms_lob.CALL);
   
   client_xml( p_client_id => v_client_id
              ,p_clob      => v_clob);

   dbms_lob.freeTemporary(v_clob);
END;

However, I just learned the hard way that IN OUT NOCOPY is only a hint, and that Oracle sometimes creates a local variable for the CLOB anyway.
A solution is to change the client_xml procedure above to

CREATE OR REPLACE PROCEDURE client_xml( p_client_id IN            NUMBER
                                       ,p_clob      IN OUT NOCOPY CLOB   ) AS
   v_rc         SYS_REFCURSOR;
   v_queryCtx   dbms_xmlquery.ctxType;
   
BEGIN
   IF NOT NVL(dbms_lob.istemporary(p_clob),0) = 1 THEN
      dbms_lob.createTemporary(p_clob, TRUE, dbms_lob.CALL);
   END IF;

   OPEN c_rc FOR
      SELECT col1
            ,col2
            ,col3
        FROM clients
       WHERE client_id = p_client_id;

   v_queryCtx := dbms_xmlgen.newContext(p_refcursor);
   p_clob     := dbms_xmlgen.getXML(v_queryCtx, 0);
 
END;

My concern is that in case Oracle does create a local variable, 2 temporaries will be created, but there will only be 1 freeTemporary.
Could this lead to a memory leak?
Or should I be safe with the solution above because I'm using dbms_lob.CALL?

Thanks,

Arnold

Edited by: Arnold vK on Jan 24, 2012 11:52 AM