Thursday, October 8, 2009

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.

27 comments:

Louis-Guillaume said...

Thanks for pointing this out!

Have a nice day!

Mark Striekwold said...

It's a great package, I even changed it so it can save the pdf inside a blob field in my database.

Regards,

Mark

Anonymous said...

It's great post but I did not found some things:
- encoding: can I use cp1250 example?
- compress: PDF is too big without compress
- TTF support
- Images: Intermedia is an Enterprise Edition option, it's not cheap product
Regards,
LL

Anonymous said...

And how would you integrate this in Apex? Being just a beginner, I'm clueless.... Could you maybe blog a step-by-step tutorial on this?

Morten Braten said...

@Anonymous: I have added a short step-by-step guide on how to use PL_FPDF from Apex, see

http://ora-00001.blogspot.com/2009/10/how-to-integrate-plfpdf-with-apex.html

- Morten

Anonymous said...

hi, can you please tell me how to install the package?

I've downloaded the zip file, it contains 2 files pkb, pks.

the procedures, function are there. then should I just remake one by one.

or apex got some import function or something like that??

thanks


gunther

Morten Braten said...

@ Gunther:

The "pks" file is the package specification, the "pkb" file is the package body.

To install from Apex, go to the "SQL Workshop" tab, click "SQL Scripts", then "Upload" and upload the two files.

The click to run the "pks" script first, and then the "pkb" script second.

Anonymous said...

Hi Morten, I've uploaded both files, but then when I tried to run the pks one, I got this error message.

ORA-06508: PL/SQL: could not find program unit being called

ERR-1024 Unable perform processing

btw I'm running oracle XE, should I comment out some procedures? if yes, would you mind to elaborate more about it.


thx

gunther

Unknown said...
This comment has been removed by the author.
Anonymous said...

Mark, do you mind sharing your codes that can save the pdf inside a blob field in your database?
Thanks.

hurol said...

Hi,

I receive the following error when I try to run. I know it is about the package but can you provide me some help?

ORA-20100: PL_FPDF error: Output : ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DEMO.PL_FPDF", line 1928
ORA-06512: at "DEMO.PL_FPDF", line 3264
ORA-06512: at "DEMO.TEST_PL_FPDF", line 100
ORA-06512: at line 2

Anonymous said...

I have the same problem

ORA-20100: PL_FPDF error: Output : ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "DEMO.PL_FPDF", line 1928
ORA-06512: at "DEMO.PL_FPDF", line 3264
ORA-06512: at "DEMO.TEST_PL_FPDF", line 100
ORA-06512: at line 2

Unknown said...

pl_fpdf is a great package.
We have been using it on oracle 10gr2, apex 3.1.
However, on Oracle11g, apex4.0, all works well except when image is included, the procedure hangs.
Has any one got any suggestions, ideas??

Morten Braten said...

@Hurol and @Anonymous: As written, the package runs into problems with the 32K limit on varchars in PL/SQL. I tried to contract the original package author about it, but never got any reply.

In my own copy of the package, I fixed this by using clobs instead of varchars to build the output.

- Morten

Morten Braten said...

@Kishor: In Oracle 11g, there are network access control lists (ACLs) on the network packages, such as UTL_HTTP. Perhaps that is why the image download fails?

- Morten

AndyRN said...
This comment has been removed by the author.
AndyRN said...

Hi. I have configured the parameter SetAutoPageBreak to true pl_fpdf.SetAutoPageBreak (true, 20);, and when the text of the second page begins with bold doesn't make until it find pl_fpdf.SetFont ('Times',' B ', 11);. How you can help me with this problem.

Anonymous said...

How do i run this run.?shall i just use execute button in toad?
where does the pdf gets stored?
what does this pl_fpdf.Output ();
mean? . is there any documentation for this.

I am trying to learn FPDF.

Thanks for a great post.
SK

Anonymous said...

Thanks for putting this together.

Can somebody please clarify how you managed to get around the 32K limit on varchar2s? I quickly hit that ceiling when trying to generate a report with a few thousand rows.

To address a couple of the other questions asked in this thread:

1. I think the idea is to execute this package using mod_plsql from a browser. If you say call helloworld it will open the PDF up in the browser.
2. A simple change to the Output procedure can instead write the pdf to a database table in a blob column. I did this by creating a new output procedure and then removed all reference to myDest. Then immediately after the loop for i in pdfDo... I did the following insert:

/*
create table test_blob (col_blob blob,
creation_date date)
*/
insert into test_blob values (v_doc, sysdate);

Anonymous said...

Has anyone implemented any of the other scripts from fdf.org? Some of the scripts look great such as the multi-cell tables, html tables, etc… Since these are written in php, we would just need to convert these to plsql.

Also because of the 32k limit the re-write to clobs is necessary.

Perhaps we could collaborate on such a project.

Pramod K Sharma said...

hi...
when i run test_pl_fpdf procedure(after creating pl_fpdf),
i've face following error:
-----------------------------------
22/15 PL/SQL: SQL Statement ignored
22/69 PL/SQL: ORA-00942: table or view does not exist
24/2 PL/SQL: Statement ignored
24/20 PLS-00364: loop index variable 'L_REC' use is invalid
25/2 PL/SQL: Statement ignored
25/20 PLS-00364: loop index variable 'L_REC' use is invalid
27/2 PL/SQL: Statement ignored
27/5 PLS-00364: loop index variable 'L_REC' use is invalid
32/2 PL/SQL: Statement ignored
32/20 PLS-00364: loop index variable 'L_REC' use is invalid


----------------
plz send the solution of it to my email id:
pramod14march@gmail.com

Morten Braten said...

@Pramod: The query in the test code references the "dba_tablespaces" view. Either make sure you have access to this view, or change the query so it references some of your own tables/columns.

- Morten

hakim said...

I used the package PL_FPDF To generate an out put PDF from Apex 4.1 but I have this output.
Why(It work for me on Apex 4.2.3)

thanks for responding


%PDF-1.3 3 0 obj <> endobj 4 0 obj <> stream 2 J 0.56 w BT /F1 12.0 Tf ET BT 31.18 781.59 Td (Une Page PDF vierge) Tj ET endstream endobj 1 0 obj <> endobj 5 0 obj <> endobj 2 0 obj << /ProcSet [/PDF /Text /ImageB /ImageC /ImageI] /Font << /F1 5 0 R >> /XObject << >> >> endobj 6 0 obj << /Producer (PL_FPDF 0.9.1 portage pour Laclasse.com par P.G. Levallois de la version 1.53 de PHP/FPDF d'Olivier Plathey.) /CreationDate (D:20111213102214) >> endobj 7 0 obj << /Type /Catalog /Pages 1 0 R /OpenAction [3 0 R /Fit] /PageLayout /SinglePage >> endobj xref 0 8 0000000000 65535 f 0000000211 00000 n 0000000400 00000 n 0000000009 00000 n 0000000087 00000 n 0000000304 00000 n 0000000504 00000 n 0000000679 00000 n trailer << /Size 8 /Root 7 0 R /Info 6 0 R >> startxref 777 %%EOF

Anonymous said...

Hello Morten,

I've looked into the Alexandria plsql and it looks really good. I'm a little caught in the middle with not being an expert pl/sql programmer.

The AS_PDF is fairly good but lacks functionality. However it has 1 or 2 things which are great. The handling of images and save pdf.

How would one go about merging AS_PDF image functions into PL_FPDF. Is there a place where to make the request for the merge?

The reason I ask, is because with Anycharts you now have a getPNG function with returns base64 incoding, but PL_FPDF doesn't support that type of image input, further more it requires a ORDImage, big minus when working on Express Editions.

Thank

Jan

Anonymous said...

can you please how can I get the file location ?
i.e., where and how a pdf file can be created ?
and how to change its destination folder ?
reply awaited...

Oracle Apex Solutions said...

Hello Morten,

I am trying to add a chart in pdf, and that chart has to be generated from the data inside database, but can't find any procedure for that. Please let me know what should I do for that.

Regards
Tauceef

Bikesh Maharjan said...

hello morten I have a problem in pl_fpdf, how do i render unicode character in pdf. I've tried addfont function and included .ttf file in fonts directory but It didn't worked. Please reply and thanks in advance