Integrating XEP with Oracle

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

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:

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:

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:

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":

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.