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.
I made some tests on Oracle XE 11g and Oracle 22.214.171.124.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 126.96.36.199.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.
JAVA XSLTLet'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.
CONCLUSIONJava 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.