Creating PDF reports from PL/SQL

Do you need to create PDF reports from PL/SQL?

There are a number of options:

• Anton Scheffer wrote a PL/SQL package called AS_PDF3 that covers all the basics: Different fonts (including TrueType), images, and tables.
• There's the commercial PL/PDF package that has more features, such as the ability to use existing PDF documents as templates for new documents, compression, encryption, and more. There's also an extra Toolkit component which allows you to extract pages from existing PDFs, combine multiple PDFs into one document, manipulate document metadata, and so on.
• Using the above solutions, the PDF document must be generated via (your own) PL/SQL code. If you are looking for a visual report editor, check out PL-JRXML2PDF from Andreas Weiden.

There's a nice video of PL-JRXML2PDF in action over at Vimeo.


Generate PDF Reports for Free in Oracle Apex Using PL-JRXML2PDF and iReport Designer from Paul the Hermit on Vimeo.


Also, if you are using Apex 4.2 or later with the Apex Listener (aka Oracle REST Data Services), you can output report regions to PDF, and customize the layout via XSL-FO templates as explained by Mark Sewtz in his blog post.

How to integrate PL_FPDF with Apex

In my previous post about the free PL_FPDF package that allows you to produce PDF documents from PL/SQL, there was a comment asking for a step-by-step guide on how to integrate this with Oracle Application Express (Apex).

This is really quite simple. By default, the pl_fpdf.output procedure will "print" your PDF document to the web browser along with a header that instructs the browser that this is a downloadable document. All you need to do is to call your procedure that generates the PDF document from a page process within Apex.

Here are the steps:

1. Start by compiling the demo procedure from my previous post into the parsing schema of your Apex application.

2. In Apex, go to your application and add a new, blank page. Let's assume the new page number is 4.

3. Under the "Page Rendering" section, add a new Process of type "PL/SQL". The name of the process can be anything, but let's call it "Produce PDF". Make sure that the point is "On load - before header".



4. In the process source, add the following PL/SQL code:

begin

  -- call the procedure to generate the PDF document and send it to the browser
  test_pl_fpdf;

  -- stop the Apex engine from running the rest of the page
  apex_application.g_unrecoverable_error := true;

end;

5. Add a link to the new page (page number 4 if you follow my assumption in step 2) from any other page in the application.

6. Run the application. Now, whenever you navigate to page 4 the PDF file will be downloaded to the browser.

That's all you need to integrate PDF generation into your Apex application.

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.