Creating PDF's with PL/SQL and itext

There are several ways to create PDF’s, on the client (e.g. PDFCreator) or on the middletier.
But to create PDF’s merely with packages in the database, that’s another story. Even Apex uses some Java on the middletier.
The product PLPDF on the other hand is a well known utility which provides this functionality (using open-source ‘itext’), but.. you have to pay for this.

So why not do it ourselves with itext, open-source (answer: a lot of work and knowledge of Java…).

What follows are the basics to use pl/sql in combination with ‘itext’.
1. Download itext-jar-file (itext-2.1.5.jar):

http://sourceforge.net/project/showfiles.php?group_id=15255&package_id=12596

Copy the jar-file to your database server

2. load these java-classes in the database.

grant create procedure to <owner>;
grant create table to <owner>;

loadjava -user exg/exg iText-2.1.5.jar

Some explanation about loadjava:
http://www.oracleutilities.com/OSUtil/loadjava.html

Check if it’s correctly loaded in the database:

select object_name, object_type, status
from dba_objects
where object_type like ‘%JAVA%’
and owner='<owner>’
and status <> ‘VALID’

3. A lot of Java is not valid, you need some other Java classes of ‘bouncycastle’ (encryption):

http://www.bouncycastle.org/latest_releases.html
downloaded: crypto-143.tar.gz (the whole bunch of stuff..)
–> loadjava -user <user>/<pw> bcmail-jdk14-143.jar
–> loadjava -user <user>/<pw> bcprov-jdk14-143.jar

4. Compile the itext-java classes in the database

–> alter java class “<java_class>” resolve;

5. Write a standard Java-package in the database.

Inspiration of the code :
http://forums.oracle.com/forums/thread.jspa?threadID=572769
(writer: Satyaki De)

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED <owner>.”Util” as package createpdf;
import java.io.FileOutputStream;
import java.io.IOException;

import com.lowagie.text.*;
import com.lowagie.text.pdf.PdfWriter;

public class Util extends Object
{
public static void Create_Pdf(String path, String src)
{
Document document = new Document();
try
{
PdfWriter.getInstance(document, new FileOutputStream(path));
document.open();
document.add(new Paragraph(src));
}
catch (DocumentException de)
{
System.err.println(de.getMessage());
}
catch (IOException ioe)
{
System.err.println(ioe.getMessage());
}
document.close();
}
};
/

6. Create a function in PL/SQL which calls this java.

CREATE OR REPLACE function <owner>.create_new_pdf(p_path in varchar2, p_src in varchar2)
return varchar2
as
language java
name ‘createpdf.Util.Create_Pdf(java.lang.String,java.lang.String) return String’;
/

7. Create directory in the database (e.g. ‘PDF’)

–> Create directory PDF as ‘/data/dir/example’;

8. Give permission to write and read in this directory

–>grant read,write on directory PDF to exg;

As SYS:

BEGIN
dbms_java.grant_permission(‘<owner>’,’SYS:java.io.FilePermission’,’/data/dir/example/*’,’read,write’);

dbms_java.grant_permission(‘<owner>’,’SYS:java.lang.RuntimePermission’,’*’,’writeFileDescriptor’
);

END;

/

commit; (do not forget !!)

9. Test it

declare
v_err varchar2( 4000 );
begin
v_err := EXG.create_new_pdf(‘/data/dir/example/test.pdf’,’!’);
end;

With thanks to Diana Veerman, developer.

By | 2009-06-20T22:30:23+00:00 June 20th, 2009|Categories: Database|Tags: , , |2 Comments

About the Author:

2 Comments

  1. Ben Li November 17, 2017 at 5:46 pm

    Job,
    Is it possible to create a PDF document (or a bulk of PDF documents) and directly put it (them) in a LOB table column (pointing to an address in a tablespace’s datafile)? I am having a need to create some smaller PDF fdocuments, based on a LOB table’s data, to update an existing LOB column of large PDF documents. I don’t want to create individual documents onto a filesystem or a directory first and then read them back into the same database, which may take quite a long time.

  2. Job November 18, 2017 at 4:08 pm

    Ben, I’m not a practicing DBA anymore, but I think you may get the job done with storing the LOB’s as Securefiles. Then you’ve got more possibilities ahead. As a starting point an explanation from Arup Nanda may help: http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html . Hope it helps.

Leave A Comment