sobota, 19. december 2015

Java vs. PL/SQL: XSL processing


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
I made some tests on Oracle XE 11g and Oracle When running test code described below oracle.exe process consumes additional 1,6 GB of RAM on Oracle XE 11g and 3GB on Oracle! 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.


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.


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.

Ni komentarjev:

Objavite komentar