Integrating XEP with Oracle

Although XEP will later run inside the Oracle database, it will first have to be installed on the server for a number of reasons: first, not all the classes included in the setup_xep?.jar file will have to be imported. Then, XEP still needs operating system directory and file access in order to cache some data and read the license and the different font files, it does not accept them if they are stored as Java objects in the database.

For the installation of XEP, refer to the appropriate documentation.

Once XEP is installed on the server, the Java classes will have to be imported into Oracle using Oracle's loadjava tool (you may certainly choose to use dbms_java.loadjava procedure inside the database, too). In order to do so, you will have to place yourself in XEP's lib directory and not use any directory references in the loadjava command line, since Oracle imports the classes exactly as they are specified on the command line, i.e. any directory specification would be integrated into the class name.

Execute the following commands to import the Java archives in the lib directory (mind that the saxon.jar archive is excluded, since sax is already included in Oracle's XDB).

loadjava -user username/password@database junit.jar

loadjava -user username/password@database xt.jar

loadjava -user username/password@database xep_your_version.jar

Mind that you shouldn't try to tell loadjava to resolve the classes, since Oracle would attempt to resolve each and every class, which leads to an incredible amount of error messages because there are many references to test classes etc. that cannot be resolved. The necessary classes will be resolved at runtime.

When you now log onto the database, the imported classes should be available in the specified user's schema, although mostly invalid. For the same reason as mentioned above, you shouldn't try to compile these classes; this will be done automatically when you use them.

The next step is to write and import a Java class that implements the XEP call. Our class "MakeXEP" offers the possibility of producing PDF as well as PS output. The file MakeXEP.java looks like this:

            //Java
            import java.io.*;
            import oracle.sql.CLOB;
            import oracle.sql.BLOB;
            import java.sql.*;
            import java.util.Properties;
            //JAXP
            import org.xml.sax.*;
            import javax.xml.transform.stream.StreamSource; 
            //XEP
            import com.renderx.xep.*;
            
            public class MakeXEP {
            
            public static void render_pdf
            (oracle.sql.CLOB FOInput,
            oracle.sql.BLOB Output)
            
            throws 
            SQLException, 
            IOException,
            com.renderx.xep.lib.ConfigurationException,
            org.xml.sax.SAXException
            {
            java.util.Properties props = new java.util.Properties();
            props.setProperty("ROOT", "<xep-root-dir>");
            javax.xml.transform.Source source =
            new StreamSource(FOInput.getAsciiStream());
            com.renderx.xep.FormatterImpl formatter =
            new com.renderx.xep.FormatterImpl(props);
            com.renderx.xep.FOTarget target =
            new com.renderx.xep.FOTarget(Output.getBinaryOutputStream(), "PDF" );
            formatter.render(source, target);
            formatter.cleanup();
            } 
            
            public static void render_ps
            (oracle.sql.CLOB FOInput,
            oracle.sql.BLOB Output)
            throws
            SQLException,
            IOException,
            com.renderx.xep.lib.ConfigurationException,
            org.xml.sax.SAXException
            {
            java.util.Properties props = new java.util.Properties();
            props.setProperty("ROOT", "<xep-root-dir>");
            javax.xml.transform.Source source =
            new StreamSource(FOInput.getAsciiStream());
            com.renderx.xep.FormatterImpl formatter =
            new com.renderx.xep.FormatterImpl(props);
            com.renderx.xep.FOTarget target =
            new com.renderx.xep.FOTarget(Output.getBinaryOutputStream(), "PS" );
            formatter.render(source, target);
            formatter.cleanup();
            }    
            }
        

This class accepts an XSL-FO document that was previously produced in the database as a Clob and produces a PDF or PS document which is then available as a Blob. Import this class with loadjava -user user/password@database MakeXEP.Java

When you look at the Java objects inside the database now, you should find this class in the specified user's schema. Compile the source and see if it's valid. Mind that when working under Oracle 10g, the database complains that you use a deprecated Java API, but it compiles the source nonetheless. (If someone finds out what this API is, please let me know).

The next steps take place inside the database. So, log onto the database as the user specified when you imported the Java classes. Now, you will have to publish the Java methods separately. The aforementioned class is published creating two stored procedures like this:

                CREATE OR REPLACE PROCEDURE render_pdf (
                FOInput CLOB,
                Output  BLOB)
                AS LANGUAGE JAVA
                NAME 'MakeXEP.render_pdf(
                oracle.sql.CLOB,
                oracle.sql.BLOB
                )'; 
                
                CREATE OR REPLACE PROCEDURE render_ps (
                FOInput CLOB,
                Output  BLOB)
                AS LANGUAGE JAVA
                NAME 'MakeXEP.render_ps(
                oracle.sql.CLOB,
                oracle.sql.BLOB
                )'; 
            

This is only a pass-through procedure, as always necessary for Java stored procedures in Oracle. Mind that the Blob you pass to this procedure will be modified and thus be available to your main procedure without the necessity to specify it as an IN OUT parameter; in fact, Java calls do not accept IN or OUT specifications for parameters.

XEP needs directory access. First, the license.txt, the fonts.xml and the font metrics files have to be read, and then XEP caches some data in a tmp directory. The directory access is not an ordinary Oracle privilege that you could just grant, but a Java IO permission that has to be granted using the dbms_java package. Mind that you will not get any notification if the grant fails (e.g. because of a non-existent directory)! You have to grant read permission on all files and directories (denoted by '-') in the XEP root directory and on the root directory itself in two steps, then write permission on the tmp directory, in a PL/SQL procedure. For PS output, you also have to grant delete permission on the tmp directory. These permissions have to be granted to the user who owns the Java objects, i.e. the same username used in the loadjava statements above:

                begin
                dbms_java.grant_permission(
                '<user>',
                'SYS:java.io.FilePermission',
                '<XEP root dir>/-',
                'read');
                dbms_java.grant_permission(
                '<user>',
                'SYS:java.io.FilePermission',
                '<XEP root dir>',
                'read');
                dbms_java.grant_permission(
                '<user>',
                'SYS:java.io.FilePermission',
                '<XEP root dir>/tmp/-',
                'write');
                dbms_java.grant_permission(
                '<user>',
                'SYS:java.io.FilePermission',
                '<XEP root dir>/tmp/-',
                'delete');
                end;
            

If you encountered no fatal errors, XEP should now be ready to run inside the database. Here's a sample procedure that produces XML and XSL, transforms them into an XSL-FO document and passes this one to XEP. Mind that a Clob is passed to XEP instead of the parser instance (which would also be possible). This is due to the fact that Oracle's Transformer does not produce an encoding header, so XEP expects a UTF-8 encoded document. We, however, are using the ISO-8859-15 character set. This unfortunately means that we have to retrieve the XSL-FO as a Clob, open another one, write an encoding header into the latter and then copy the contents of the former into it. Quite a nuisance, but it's the only possibility we found. If someone finds a better way, please let me know.

The resulting PDF/PS data is retrieved into a Blob prepared for that purpose. You can then use this Blob for whatever purpose you want. In our case, it is sent over the intranet.

The following package implements all the aforementioned conversion steps, except for the production of the XML data, producing a PDF document. Mind that the schema names have to be replaced accordingly, in our case "oracle" owns the java objects, while "printmaster" owns the stylesheet table; the package (the declaration of which is left out) is owned by "webmaster":

                CREATE OR REPLACE package body kl_test is
                
                -- The following is for test purposes only!!!
                FUNCTION authorize RETURN boolean
                IS
                BEGIN
                RETURN true;
                END;
                
                
                PROCEDURE pdf_output IS
                xml_clob  clob;
                xsl_clob  clob;
                fo_clob   clob;
                temp_clob clob;
                pdf_blob  blob;
                
                l_parser      dbms_xmlparser.Parser;
                l_styleparser dbms_xmlparser.Parser;
                l_processor   dbms_xslprocessor.Processor;
                l_stylesheet  dbms_xslprocessor.Stylesheet;
                
                BEGIN
                
                -- set up the temporary Lobs
                dbms_lob.createtemporary(xml_clob, true);
                dbms_lob.createtemporary(xsl_clob, true);
                dbms_lob.createtemporary(fo_clob, true);
                dbms_lob.createtemporary(temp_clob, true);
                dbms_lob.createtemporary(pdf_blob, true);
                
                -- fill the xml_clob with appropriate data (left out here)
                [...]
                
                -- parse the xml document
                l_parser := dbms_xmlparser.newParser;
                dbms_xmlparser.parseClob(l_parser, xml_clob);
                
                -- retrieve the stylesheet from the database where it is stored as a Clob
                -- in another user's schema and parse it
                SELECT stylesheettext
                INTO   xsl_clob
                FROM   printmaster.stylesheet
                WHERE  stylesheetnr = 1;
                l_styleparser := dbms_xmlparser.newParser;
                dbms_xmlparser.parseClob(l_styleparser, xsl_clob);
                l_processor := dbms_xslprocessor.newProcessor();
                l_stylesheet :=  
                dbms_xslprocessor.newStylesheet(dbms_xmlparser.getDocument(l_styleparser), 
                null);
                
                -- transform the xml data using the stylesheet
                dbms_xslprocessor.processXSL
                (l_processor, l_stylesheet, dbms_xmlparser.getDocument(l_parser), 
                temp_clob);
                
                -- some cleaning up
                dbms_xmlparser.freeParser(l_parser);
                dbms_xslprocessor.freeProcessor(l_processor);
                dbms_xmlparser.freeParser(l_styleparser);
                
                -- The Oracle parser won't insert the encoding header, so it has to be done
                -- manually, then append the fo data to the Clob
                dbms_lob.append(fo_clob, '<?xml version="1.0" encoding="ISO-8859-15"?>');
                dbms_lob.copy(fo_clob, temp_clob, length(temp_clob), length(fo_clob)+1, 1);
                
                -- start XEP
                oracle.render_pdf(fo_clob, pdf_blob); 
                
                -- PDF is now available as a Blob. 
                -- It can now e.g. be sent to the client:
                owa_util.mime_header('application/pdf');
                htp.print ('Content-length: '||dbms_lob.getlength(pdf_blob));
                wpg_docload.download_file(pdf_blob);
                
                -- free the temporary Lobs
                dbms_lob.freetemporary(xml_clob);
                dbms_lob.freetemporary(xsl_clob);
                dbms_lob.freetemporary(fo_clob);
                dbms_lob.freetemporary(temp_clob);
                dbms_lob.freetemporary(pdf_blob);
                
                end pdf_output;
                end kl_test;