Creating XML via the DB

Just some ways to produce XML from an Oracle DB using only SQL and PL/SQL. I'm sure this list is incomplete so I will expand it as I remember others. This is just what was coming to mind before writing this entry

• SQL/XML (XMLElement, XMLForest, XMLAgg)
• DBMS_XMLDOM
• XQUERY
• DBMS_XMLGEN
• DBMS_XMLQuery
• TRANSFORM/XMLTransform
• CreateXML/XMLType

Basically this serves as a reminder of what to cover in the future.

XMLType/XMLTransform and parameters

While spending time in the OTN XML DB forums, I ran across yet another thread where I learned something new. The thread was Add Namespaces via XQuery to an XML Instance . Besides relearning I still have a lot to learn about XQuery, I learned that the XMLTransform SQL operator accepts a third parm. This makes sense given the two operations, the PL/SQL .transform and SQL XMLTransform, perform the same task. If you look at the 11.2 documentation for XMLTransformation, it still shows it accepts only two parms. The XMLType.Transform function shows a third parm, parammap, which is a VARCHAR2.

Given my lack of understanding of the third parm in general, I decided to research it. From what little I was able to turn up, it is described in more detail in Metalink. I am currently without Metalink access so I'm left to wonder as well what is said. I was able to piece together the following examples of a few ways to send in multiple parms.

Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
 
SQL> set serveroutput on;
SQL> 
SQL> DECLARE
  2    l_xml      XMLTYPE := XMLTYPE('<root/>');
  3    l_xsd      XMLTYPE;
  4    l_new_xml  XMLTYPE;
  5    l_val1     VARCHAR2(5) := 'val1';
  6    l_val2     VARCHAR2(5) := 'val 2';
  7  BEGIN
  8    l_xsd := XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" 
                                            xmlns:fo="http://www.w3.org/1999/XSL/Format" 
                                            exclude-result-prefixes="fo">
  9      <xsl:output method="xml"/>
 10  
 11      <xsl:param name="par1"/>
 12      <xsl:param name="par2"/>
 13  
 14      <xsl:template match="/">
 15          <output>
 16              <param1><xsl:value-of select="$par1"/></param1>
 17              <param2><xsl:value-of select="$par2"/></param2>
 18          </output>
 19      </xsl:template>
 20  </xsl:stylesheet>');
 21  
 22    dbms_output.put_line('#1');  -- hard coded strings
 23    l_new_xml := l_xml.transform(l_xsd, 'par1="''val1''"  par2="''val 2''"');
 24    dbms_output.put_line(l_new_xml.getStringVal());
 25  
 26    dbms_output.put_line('#2');  -- hard coded strings
 27    l_new_xml := l_xml.transform(l_xsd, q'{par1="'val1'" par2="'val 2'"}');
 28    dbms_output.put_line(l_new_xml.getStringVal());
 29  
 30    dbms_output.put_line('#3');  -- passing in variables
 31    l_new_xml := l_xml.transform(l_xsd, 'par1="''' || l_val1 || '''" par2="''' || l_val2 || '''"');
 32    dbms_output.put_line(l_new_xml.getStringVal());
 33  
 34    dbms_output.put_line('#4');  -- hard coded string/number
 35    l_new_xml := l_xml.transform(l_xsd, 'par1="''val1''"  par2="2"');
 36    dbms_output.put_line(l_new_xml.getStringVal());
 37  
 38    dbms_output.put_line('#5');  -- hard coded string/number
 39    l_new_xml := l_xml.transform(l_xsd, q'{par1="'val1'" par2="2"}');
 40    dbms_output.put_line(l_new_xml.getStringVal());
 41  END;
 42  /
 
#1
<output>
<param1>val1</param1>
<param2>val 2</param2>
</output>

#2
<output>
<param1>val1</param1>
<param2>val 2</param2>
</output>

#3
<output>
<param1>val1</param1>
<param2>val 2</param2>
</output>

#4
<output>
<param1>val1</param1>
<param2>2</param2>
</output>

#5
<output>
<param1>val1</param1>
<param2>2</param2>
</output>
 
PL/SQL procedure successfully completed

As the Oracle documentation says, the parameters are a "string of name=value pairs". What the online documentation leaves out are examples regarding usage.

So what can we learn from the working examples above?

• If the value is a string, the right side formatted is as "'value'". Note it is wrapped with a leading/trailing '. Depending upon how you setup your parameter, this may mean the use of two single quotes to insert one single quote in the resolved string.
  
• If the value is a number, the right side formatted is as "value". No leading/trailing ' is needed. You can treat the number as a string but then the above rule applies.
  
• Spaces are allowed within a value.
  
• Everything can be treated as a string or number that is being passed into the .xsl
  
• A space, one or more, is used to separate pairs.
  
• The common error reported when not following the above noticed rules was an "ORA-31020: The operation is not allowed, Reason: Invalid XSL Parameter or its Value". I did see a few others, including killing my session as the OTN forum thread noted when randomly throwing data in the third parm.
  

I'm not sure where the q'{}' structure comes from but it means no need for putting double single quotes around the string values as shown in #2 and #5.

As that forum post shows, and the below example as well, you can pass the third parameter into XMLTransform and it will work appropriately.

SQL> SELECT XMLSerialize(DOCUMENT
  2           XMLTransform(XMLTYPE('<root/>'),
  3                        XMLTYPE('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  4                                              xmlns:fo="http://www.w3.org/1999/XSL/Format"
  5                                              exclude-result-prefixes="fo">
  6      <xsl:output method="xml"/>
  7  
  8     <xsl:param name="par1"/>
  9     <xsl:param name="par2"/>
 10  
 11     <xsl:template match="/">
 12        <output>
 13           <param1><xsl:value-of select="$par1"/></param1>
 14           <param2><xsl:value-of select="$par2"/></param2>
 15          </output>
 16      </xsl:template>
 17  </xsl:stylesheet>'),
 18         'par1="''val1''"  par2="2"')
 19           AS CLOB INDENT) xsl_output
 20    FROM dual;
 
XSL_OUTPUT
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="ISO-8859-1"?>
<output>
  <param1>val1</param1>
  <param2>2</param2>
</output>

You can replace the third parameter with any of the examples shown above, assuming you setup variable for #3, and the results will be identical to that shown above. Note: I simply used XMLSerialize to format the output similar to how the PL/SQL code formatted it.

So now we know the basics for how to pass parameters to stylesheet and that you can do it with XMLTransform as well, even though the documentation doesn't specify it.

Memory Leaks

The thought for this posting has been in my head for a while and then this week this post came up on the OTN XML DB Forums ... XMLTYPE.createxml - how to free memory?

I knew when working with DOMDocuments that there was a .FreeDocument() but I didn't know of a similar concept for XMLType. From the testing I did given what the poster was seeing, it looks to be a bug only when using a sys_refcursor as input when doing an XMLTYPE conversion. It could impact BFILENAME as well but I need to get around to testing that at some point.

This brought back memories from a few years back of when I stumbled across a memory leak in the code one of our clients was using on 10.2.0.3. We knew we had memory leaks somewhere in the app code because we would need to restart our drivers (sessions that ran 24x7) every few days else the higher processing volume threads would eat up memory and kill the node. This was in a RAC so we got to watch the fail-over work as intended. Not something you want to do on a regular basis in a production system. We put in a process to stop/start those threads every so often to release memory while we tried to figure out where the leak was.

At that time a task came up where I had to do something as a one time job, so I hacked together a script that used a lot of production code, tested it and finally let it loose in Production. I was using DBMS_APPLICATION_INFO to log job progress and I noticed that over time, the job would start slowing down. I could stop and restart the job and it would run fast and then start slowing down again. After a lot of research and time stripping out pieces of code and rerunning and not seeing any progress, I finally figured out the code was missing some dbms_xmldom.FreeDocument statements that the documentation tells you to use.

Ah, the cause of the memory leak. Reviewed the code, plugged those in where missing, go back to testing my script and notice no difference. Huh? I stripped the script down even further till I had something like the following.

DECLARE 
   l_dom_doc   dbms_xmldom.DOMDocument;

   PROCEDURE p_local_open_close IS
   BEGIN
      l_dom_doc := dbms_xmldom.newDOMDocument;
      dbms_xmldom.FreeDocument(l_dom_doc);
   END p_local_open_close;

BEGIN
   DBMS_APPLICATION_INFO.SET_MODULE(module_name => 'user_domDocument_mem_leak_test',
                                    action_name => 'SLEEPING');
   dbms_lock.sleep(20);
   /* Sleeps so have time to find OS PID via
   select s.sid, s.process, s.serial#, s.module, s.action, p.spid os_pid
   from gv$session s,
        gv$process p
   where s.module = 'user_domDocument_mem_leak_test'
     and s.paddr = p.addr;
   */
   dbms_application_info.set_action('RUNNING');
   FOR i IN 1..700000  -- adjust as needed for your system
   LOOP
     p_local_open_close;
   END LOOP;
   dbms_application_info.set_action('COMPLETE');
  
END;

Running that on both 10.2.0.1 (our Development at the time) and 10.2.0.3 (Test/Production) would result in memory usage growing consistently while the script was running. We had our client open a SR with Oracle and they did confirm it was a bug. At some point later, Oracle provided the patch to fix this bug and all was good. I think this fix was included in the .4 release but I've lost the bug number for reference purposes. I think it stemmed from some of the JAVA used under the covers by dbms_xmldom based on what I learned from Oracle's responses regarding .freeDocument on this SR and another I had open with them regarding side effects.

One other interesting thing we saw is that the Development ran on RedHat Linux and Test/Production on SunOS. When the script finished running in Dev, the amount of memory that was used would be freed up even though the session/thread was still alive. On SunOS, we had to kill the session/thread before the memory would free up. I was always interested in whether that was caused by the .1 and .3 difference or the OS difference but we never had the opportunity to sync the version up until both went to .4