Free PDF package for PL/SQL

I'm sure you've heard about PL/PDF, the PL/SQL-based solution that allows you to create PDF documents directly from your database. PL/PDF is a good product, but it is not free (the current license price is USD 600 per database server).



However, I just came across a free alternative called PL_FPDF, which is a PL/SQL port of the PHP-based FPDF. The latest version of PL_PDF seems to have been released about a year ago, and I can't believe I haven't seen or heard about it before now... !

UPDATE, MARCH 2011: Also check out this alternative PDF generation package by Anton Scheffer. It seems simpler and more robust than the PL_FPDF package, but may lack certain features. Be sure to evaluate both!

PL_FPDF is just a single package, so installation is a snap. Note that it uses the ORDImage data type to place images in PDF documents, so if you are running Oracle XE (which doesn't include the ORDImage data type), you need to comment out the few procedures that deal with this data type (and obviously you will not be able to include images in your PDF documents...).

After a few hours of experimentation, I was able to produce a semi-complex PDF document that tests a number of features in PL_FPDF. My test script is included below for your convenience. Note that the default assumption is that output goes to the web browser via the HTP package, but it should be trivial to add a procedure that saves the resulting BLOB to a database table instead.

Note: Since PL_FPDF is based on FPDF, I found the online documentation for FPDF (see link above) very useful in order to find out how the API for PL_FPDF works.

create or replace procedure test_pl_fpdf
as
l_text varchar2(32000) := 'First, reduce actual complexity by eliminating unnecessary features and then hiding what you can''t eliminate. Secondly, reduce perceived complexity by minimizing visual noise and reusing elements. And finally, use the blank state to help orient users. Minimizing complexity in the user interface will help people learn your application more quickly, use it more effectively and be happier all the while.

As jazz musician Charles Mingus said, "Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that''s creativity."';

begin

-- create document in portrait mode, use measurements in millimeters (mm), and use the A4 page format
pl_fpdf.FPDF('P','mm','A4');
pl_fpdf.openpdf;

-- display full page, two facing pages 
pl_fpdf.setdisplaymode ('fullpage', 'two');

-- a procedure that will be called at the end of each page
-- pl_fpdf.setfooterproc('demo.test_pdf_footer');  

pl_fpdf.AddPage();

-- set up some headers
pl_fpdf.SetFillColor(255,128,128);
pl_fpdf.SetFont('Arial','B',14);
--this header will be filled with the background color 
pl_fpdf.cell(40,7,'TABLESPACE', 1, 0, 'L', pfill => 1);
pl_fpdf.cell(40,7,'CONTENTS', 1);
pl_fpdf.cell(40,7,'INITIAL EXTENT', 1, 1);

pl_fpdf.SetFont('Arial','',14);

for l_rec in (select tablespace_name, contents, initial_extent from dba_tablespaces order by 1) loop

pl_fpdf.settextcolor(0,0,0);
pl_fpdf.cell(40,7,l_rec.tablespace_name,'B');
pl_fpdf.cell(40,7,l_rec.contents,'B');
-- some conditional formatting
if l_rec.initial_extent > 66000 then
pl_fpdf.settextcolor(255,0,0);
else
pl_fpdf.settextcolor(0,0,0);
end if;
pl_fpdf.cell(40,7,l_rec.initial_extent,'B', 1, 'R');

end loop;

-- a page that shows how to position chunks of text (with automatic line breaks) on the page

pl_fpdf.AddPage();

pl_fpdf.SetFont('Arial','B',16);
pl_fpdf.setxy (100, 20);
pl_fpdf.Cell(10,10,'Cool Quote 1:');

pl_fpdf.setxy (100, 50);
pl_fpdf.SetFont('Times','',12);
pl_fpdf.multicell(100,0,l_text);


pl_fpdf.SetFont('Arial','B',16);
pl_fpdf.setxy (10, 130);
pl_fpdf.Cell(10,10,'Cool Quote 2:');

pl_fpdf.setxy (10, 150);
pl_fpdf.SetFont('Times','',12);
pl_fpdf.multicell(100,0,l_text);

-- a page that demonstrates some simple drawing with lines and rectangles

pl_fpdf.AddPage();

pl_fpdf.SetFont('Arial','B',14);
pl_fpdf.Cell(0,0,'And now, some beautiful line art...',0,1,'C');

pl_fpdf.line (10,10, 50, 50);
pl_fpdf.rect (50,50, 50, 50);
pl_fpdf.setdrawcolor(0,255,0);
pl_fpdf.line (150,150, 50, 50);

pl_fpdf.setdrawcolor(0,0,0);

-- a simple table of employees, without headings

pl_fpdf.AddPage();

for l_rec in (select empno, ename from emp order by ename) loop

pl_fpdf.cell(40,7,l_rec.empno, 'B');
pl_fpdf.cell(40,7, l_rec.ename, 'B', 1);

end loop;


pl_fpdf.SetFont('Arial','B',48);
pl_fpdf.settextcolor(0,0,255);

pl_fpdf.setxy (100, 250);

pl_fpdf.cell(80,10,'THE END', palign => 'C');

pl_fpdf.Output();

end test_pl_fpdf;

Conclusion: While PL/PDF is more advanced in terms of features, PL_FPDF might be for you if you just need some simple PDF reports in your application.