Tuesday, September 17, 2013

Passing XMLType By Reference

This post was inspired by the Daily PL/SQL Quiz for 22 August 2013 over on the PL/SQL Challenge.  That quiz was testing concepts related to setting OUT parameters in a called procedure, but one of the quizzes was performing some *evil*.  The procedure was changing a global VARCHAR2 variable that had also been passed to the procedure as an IN parameter.  When the procedure looked at the local IN variable, it saw the change.  This got me to wondering whether this would apply to an XMLType variables as well.
Per Subprogram Parameter Modes and Subprogram Parameter Aliasing with Parameters Passed by Reference it should, but why not test it to be sure.  So I setup a simple test on 11.1 to verify it

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
SQL> set serverout on
SQL> DECLARE
  2    g_xmltype   XMLTYPE;
  3  
  4    FUNCTION return_col_value(i_xml  IN XMLTYPE)
  5    RETURN VARCHAR2
  6    IS
  7       l_value   VARCHAR2(10);
  8    BEGIN
  9       g_xmltype := XMLTYPE('<root><c1>b</c1></root>');
 10       SELECT c1
 11         INTO l_value
 12         FROM XMLTable('/root'
 13                       PASSING i_xml
 14                       COLUMNS
 15                       c1  VARCHAR2(10) PATH 'c1');
 16  
 17       RETURN l_value;
 18    END return_col_value;
 19  BEGIN
 20    g_xmltype := XMLTYPE('<root><c1>a</c1></root>');
 21    dbms_output.put_line(return_col_value(g_xmltype));
 22  END;
 23  /
 
b
 
PL/SQL procedure successfully completed

Note that line 9 sets the global variable and the SELECT on line 10 reads from the local IN variable instead.  The function returns the value that was set on line 9 instead of what was set on line 20.  Had the function not been able to see the assignment on line 9, then the function would have returned "a" as that is what the XML looked like on line 20 when the function was called.  This confirms that the XMLType variable was passed by reference, as a by value passing would not have seen the change made on line 9.

You may be thinking, well all that was in the same anonymous block of code so it was easy for Oracle to figure that out and treat it by reference.  Following is another example, that would be more common to this situation, where the XMLType variable resides outside the block of code that is the function or block of code calling the function.

SQL> CREATE OR REPLACE PACKAGE hold_value
  2  IS
  3     g_xmltype   XMLTYPE;
  4  END;
  5  /
 
Package created
SQL> CREATE OR REPLACE FUNCTION return_col_value(i_xml  IN XMLTYPE)
  2  RETURN VARCHAR2
  3  IS
  4     l_value   VARCHAR2(10);
  5  BEGIN
  6     hold_value.g_xmltype := XMLTYPE('<root><c1>b</c1></root>');
  7     SELECT c1
  8       INTO l_value
  9       FROM XMLTable('/root'
 10                     PASSING i_xml
 11                     COLUMNS
 12                     c1  VARCHAR2(10) PATH 'c1');
 13  
 14     RETURN l_value;
 15  END return_col_value;
 16  /
 
Function created
SQL> BEGIN
  2    hold_value.g_xmltype := XMLTYPE('<root><c1>a</c1></root>');
  3    dbms_output.put_line(return_col_value(hold_value.g_xmltype));
  4  END;
  5  /
 
b
 
PL/SQL procedure successfully completed

Same result as before so the change made on line 6 was seen in the SELECT statement and returned by the function, even though the package variable was modified and not the local variable the SELECT statement was using.  So as above, Oracle is passing XMLType variables defined as IN by reference instead of by value.  This is in agreement with Oracle documention.

One final note.  Don't do something like this in production code.  This would be called a nasty side-effect and one not obvious unless you look closely at the code.