Monthly Archives: December 2012

Formatting the output from dbms_xplan.display_cursor

I have in the past struggled with the myriad of formatting options available with dbms_xplan.display_cursor so I was pleased to learn about a quick and easy to remember way of formatting the results at this years UKOUG conference.

First execute the SQL statement of interest including the gather_plan_statistics hint:

SELECT /*+ gather_plan_statistics */ e.*
FROM   hr.employees e
WHERE  e.first_name = 'John'
AND    e.last_name  = 'Chen'
/

Then run dbms_xplan.display_cursor:

SELECT *
FROM   table(dbms_xplan.display_cursor(format => 'allstats last'))
/

The key point is that ‘allstats last’ is used to format the output. This is a short cut for the formatting options of ‘IOSTATS MEMSTATS’. The inclusion of last ensures that the details of the last SQL statement executed is shown.

The example shown above will work on 11g R1 onwards because of the use of a named parameter in SQL. If you are using 10g you need to remove the change the call dbms_xplan.display_cursor to dbms_xplan.display_cursor(null, null, ‘allstats last’))

Once run you will see the output of:

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  abd665w8zn3f8, child number 0                                                                                                                                                                                                                                            
-------------------------------------                                                                                                                                                                                                                                                              
SELECT /*+  gather_plan_statistics */ e.* FROM   hr.employees e WHERE                                                                                                                                                                                          
e.first_name = 'John' AND      e.last_name = 'Chen'                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                             
Plan hash value: 2077747057                                                                                                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                             
-----------------------------------------------------------------------------------------------------                                                                                                                                                                              
| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                                                                                                                           
-----------------------------------------------------------------------------------------------------                                                                                                                                                                              
|   0 | SELECT STATEMENT            |             |      1 |        |      1 |00:00:00.01 |       2 |                                                                                                                                                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |      1 |      1 |      1 |00:00:00.01 |       2 |                                                                                                                                                   
|*  2 |   INDEX RANGE SCAN          | EMP_NAME_IX |      1 |      1 |      1 |00:00:00.01 |       1 |                                                                                                                                                        
-----------------------------------------------------------------------------------------------------                                                                                                                                                                              
                                                                                                                                                                                                                                                                                                             
Predicate Information (identified by operation id):                                                                                                                                                                                                                              
---------------------------------------------------                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                                                             
   2 - access("E"."LAST_NAME"='Chen' AND "E"."FIRST_NAME"='John')                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                             

 20 rows selected 

Different methods to perform XSLT from PL/SQL

In this article I will demonstrate several different methods you can use to perform XSLT from within PL/SQL.

The examples were built using Oracle Database 11.2.0.1.0 and SQL Developer 3.2

The examples are based on this XML document….

<?xml version="1.0"?>
<ROWSET>
 <ROW>
 <EMPNO>7566</EMPNO>
 <ENAME>JONES</ENAME>
 <JOB>MANAGER</JOB>
 <MGR>7839</MGR>
 <HIREDATE>02-APR-1981</HIREDATE>
 <SAL>2975</SAL>
 <DEPTNO>20</DEPTNO>
 <VALID>N</VALID>
 </ROW>
 <ROW>
 <EMPNO>7788</EMPNO>
 <ENAME>SCOTT</ENAME>
 <JOB>ANALYST</JOB>
 <MGR>7566</MGR>
 <HIREDATE>19-APR-1987</HIREDATE>
 <SAL>3000</SAL>
 <DEPTNO>20</DEPTNO>
 <VALID>N</VALID>
 </ROW>
</ROWSET>

and this XSLT document….

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
 <xsl:template match="/">
 <html>
 <body>
 <xsl:for-each select="ROWSET/ROW">
 <h2><xsl:value-of select="ENAME"/></h2>
 </xsl:for-each>
 </body>
 </html>
 </xsl:template>
</xsl:stylesheet>

XMLTRANSFORM

OK, so the first example is actually SQL rather than PL/SQL! XMTRANSFORM is a SQL Function that you can call from PL/SQL.  It accepts two arguments, both of which need to be XMLTYPE, one being the XML document that you want to transform and the other is the XSLT document.

Here is an example of it using the XML and XSLT files above:

DECLARE

l_xml XMLTYPE;
l_xsl XMLTYPE;
l_transformed XMLTYPE;

BEGIN

   l_xml := XMLTYPE.CREATEXML('<?xml version="1.0"?><ROWSET><ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-1981</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-1987</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-1987</HIREDATE><SAL>1100</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-1981</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW></ROWSET>');

   l_xsl := XMLTYPE.CREATEXML('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"> <html><body> <xsl:for-each select="ROWSET/ROW"> <h2><xsl:value-of select="ENAME"/></h2> </xsl:for-each> </body></html></xsl:template></xsl:stylesheet>');

   SELECT XMLTRANSFORM(l_xml, l_xsl)
   INTO l_transformed
   FROM dual;

   DBMS_OUTPUT.PUT_LINE(l_transformed.getstringval());

END;
/
  • lines 3 – 5 I declare three XMLTYPE variables.
  • line 9 using the CREATEXML method I create a valid XML Document containing the source XML document and assign it to the l_xml variable
  • line 11 again using CREATEXML I create a valid XML document containing the XSLT stylesheet and assign it to the variable l_xsl
  • lines 13 – 15 I perform the XSLT transformation using XMLTRANSFORM. The result of which is placed in the variable l_transformed.
  • line 17 I output the contents of l_transformed using the getstringval method.

When the anonymous block is run you will see that the XML document has been transformed into HTML and only includes the values from ENAME

<html>
 <body>
 <h2>JONES</h2>
 <h2>SCOTT</h2>
 <h2>ADAMS</h2>
 <h2>FORD</h2>
 </body>
</html>

Transform member function of the XMLTYPE

The Oracle XMLTYPE has a member function that you can use for transforming your XML documents. Here is an example of it being used:

DECLARE

l_xml XMLTYPE;
l_xsl XMLTYPE;
l_transformed XMLTYPE;

BEGIN

 l_xml := XMLTYPE.CREATEXML('<?xml version="1.0"?><ROWSET><ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-1981</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-1987</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-1987</HIREDATE><SAL>1100</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-1981</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW></ROWSET>');

 l_xsl := XMLTYPE.CREATEXML('<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"> <html><body> <xsl:for-each select="ROWSET/ROW"> <h2><xsl:value-of select="ENAME"/></h2> </xsl:for-each> </body></html></xsl:template></xsl:stylesheet>');

 l_transformed := l_xml.transform(xsl => l_xsl);

 DBMS_OUTPUT.PUT_LINE(l_transformed.getstringval());

END;
/
  •  Lines 1 – 11, there is no difference from the example used to demostrate XMLTRANSFORM
  • At line 13 I call the TRANSFORM function of l_xml passing it the variable containing the XSLT document.

The output is shown below:

<html>
 <body>
 <h2>JONES</h2>
 <h2>SCOTT</h2>
 <h2>ADAMS</h2>
 <h2>FORD</h2>
 </body>
</html>

DBMS_XSLPROCESSOR

Given the relative ease of use of the previous two methods, using DBMS_XSLPROCESSOR requires a lot more code to transform an XML document. One advantage (the only?) is that this method doesn’t require the use of XMLTYPE.

The example below is taken from the Oracle documentation and modified to use the XML and XSLT documents that have been used throughout this post.

DECLARE

l_xml                     VARCHAR2(4000);
l_xsl                     VARCHAR2(4000);
l_parser                  dbms_xmlparser.parser;
l_xml_dom_document        dbms_xmldom.domdocument;
l_xslt_dom_document       dbms_xmldom.domdocument;
l_xslprocessor_ss_type    dbms_xslprocessor.stylesheet;
l_dom_doc_fragment        dbms_xmldom.domdocumentfragment;
l_dom_node                dbms_xmldom.domnode;
l_xsl_processor           dbms_xslprocessor.processor;
l_buffer                  VARCHAR2(2000);

BEGIN

 l_xml :='<?xml version="1.0"?><ROWSET><ROW><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><MGR>7839</MGR><HIREDATE>02-APR-1981</HIREDATE><SAL>2975</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>19-APR-1987</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><MGR>7788</MGR><HIREDATE>23-MAY-1987</HIREDATE><SAL>1100</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW><ROW><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><MGR>7566</MGR><HIREDATE>03-DEC-1981</HIREDATE><SAL>3000</SAL><DEPTNO>20</DEPTNO><VALID>N</VALID></ROW></ROWSET>';

 l_xsl := '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"><xsl:template match="/"> <html><body> <xsl:for-each select="ROWSET/ROW"> <h2><xsl:value-of select="ENAME"/></h2> </xsl:for-each> </body></html></xsl:template></xsl:stylesheet>';

 l_parser := dbms_xmlparser.newparser;

 dbms_xmlparser.parsebuffer(l_parser, l_xml);

 l_xml_dom_document := dbms_xmlparser.getdocument(l_parser);

 dbms_xmlparser.parsebuffer(l_parser, l_xsl);

 l_xslt_dom_document := dbms_xmlparser.getDocument(l_parser);

 l_xslprocessor_ss_type := dbms_xslprocessor.newStyleSheet(l_xslt_dom_document, '');

 l_xsl_processor := dbms_xslprocessor.newprocessor;

 l_dom_doc_fragment := dbms_xslprocessor.processXSL(l_xsl_processor, l_xslprocessor_ss_type, l_xml_dom_document);

 l_dom_node := dbms_xmldom.makeNode(l_dom_doc_fragment);

 dbms_xmldom.writeToBuffer(l_dom_node, l_buffer);

 dbms_output.put_line(l_buffer);

 dbms_xmldom.freedocument(l_xml_dom_document);
 dbms_xmldom.freedocument(l_xslt_dom_document);
 dbms_xmldom.freedocfrag(l_dom_doc_fragment);
 dbms_xmlparser.freeparser(l_parser);
 dbms_xslprocessor.freeprocessor(l_xsl_processor);

END;
/

If you run this code you will see the expected output of:

<html>
 <body>
 <h2>JONES</h2>
 <h2>SCOTT</h2>
 <h2>ADAMS</h2>
 <h2>FORD</h2>
 </body>
</html>

Summary

This post has shown several different methods that you can use to transform your XML documents using XSLT from within PL/SQL.