a step-by-step guide by Jochen Noy, M.A.Ruhr University Bochum (Germany), Computer Centre


Table of Contents

Introduction
System Configuration
Convention
The Integration Step

Introduction

This document explains how we integrated XEP into an existing Oracle system. The reason was that we needed a possibility to create PDF documents from data stored in the database and send them over the intranet directly without having to store them as files. This required the possibility to use XEP inside the Oracle database as a Java stored procedure.

System Configuration

The implementation of the XEP integration took place on Compaq Blade 20P servers under RedHat Enterprise Linux AS 2.1 and Oracle 9i, currently we are changing this system to RedHat Enterprise Linux AS 3.0 and Oracle 10g. The integration appeared to be possible without any changes under both configurations, although Oracle 10g disapproves of the use of some Java APIs, as mentioned below.

We also use the Oracle Internet Application Server; users log onto the database via web interfaces and will get the PDF documents via http once the whole application is implemented. In order to import the XEP Java classes, of course your Oracle environment has to be correctly set, especially the ORACLE_SID parameter, if you are running several databases.

In our database, the data tables, the Java objects and the PL/SQL executables belong to different user schemas for security and clarity reasons. The granting of access and execution rights is not mentioned in this document, since this is considered trivial. In our application, an XSL stylesheet is stored in the database as a Character Large Object (Clob), and the XML data is produced by querying the database and then stored in another Clob. These two Clobs are parsed (by Oracle) to obtain an XSL-FO document (also a Clob), which is then passed to XEP.

Convention

In order to integrate XEP, you will have to issue commands on operating system level, which will be printed in sans serif font. You will also need Java code which will be printed in constant width font, and finally you will need PL/SQL commands and code which will be printed in constant width italics. Other conventions are considered to be self-explaining.

The Integration Step

  1. 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.

  2. 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.

  3. 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();
       }    
    }
            
  4. 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).

  5. 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.

  6. 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;
            
  7. 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;
            

The routine filling the xml_clob with xml data is left out here, since in our case it is highly complicated. You can produce your xml data using Oracle's dbms_xmlgen or dbms_xmlquery packages; however, to us they seemed to be far too complicated to handle, so that we produce XML data manually by appending all tags in the appropriate places.

That's it; you can now produce PDF or PS documents inside your Oracle database.