Java vs. PL/SQL: XSL processing

PL/SQL XSLT

Different methods to perform XSLT from PL/SQL are described in this excellent post. In the following document I will talk only about DBMS_XSLPROCESSOR for two reasons:

• This is the only general XSL processor in PL/SQL. You can get non XML output like HTML or text and you can pass external parameters. This cannot be done with other two methods.
• Memory behavior described in this post can be found in all three methods.

During Oracle APEX plugin development I found out possible non optimal memory usage in Oracle PL/SQL XML packages. Similar problems are discussed in Oracle Forum and stackoverflow.com.
I made some tests on Oracle XE 11g and Oracle 11.2.0.3.0. When running test code described below oracle.exe process consumes additional 1,6 GB of RAM on Oracle XE 11g and 3GB on Oracle 11.2.0.3.0! Fortunately memory is completely released after returning from PL/SQL block. It takes 43 seconds to complete XSLT on Oracle XE 11g installed on Windows 7 machine. 
After extracting XSL processing.rar file you will receive three files:

• dbms_xslprocessor.sql with PL/SQL code for XSL transforming Persons10000_new.xml to Excel 2003 spreadsheet.
• Persons10000_new.xml with data about 10000 imaginary persons. Data was fetched from Oracle database table.
• Excel2003BreakEnabledTypes.xslt: XSLT for transforming XML with proper XSD schema to strongly typed Excel 2003 spreadsheet.

To successfully run dbms_xslprocessor.sql script user must have read/write access to some Oracle directory (XMLDIR in my case). Before running script you should copy XML and XSLT files to that directory and edit directory name in script. After running script you should get Excel 2003 spreadsheet file named Persons.xls.

JAVA XSLT

Let's check how Java XSL transforms the same Persons10000_new.xml file and how much memory spends.You can download java source code, Eclipse project and executable JavaXSLT.jar here. You can just run java executable with
java -jar JavaXSLT.jar Persons10000_new.xml Excel2003BreakEnabledTypes.xslt Persons10000.xls
or you can also examine source code. XSLT working horse is javax.xml.transform.Transformer class called from JavaXSLT.transform static method.
Results are impressive compared to PL/SQL.  It takes just few seconds to generate Excel spreadsheet. New java.exe process is created. Process consumes about 120MB of RAM.

CONCLUSION

Java is far more suitable for XSL processing than PL/SQL. That's why XSL and FO processing usually takes place on java application servers and not on Oracle DB server.
PL/SQL is OK for smaller documents. Good point of PL/SQL XSLT is no network traffic is needed.