Home > [old] Information > Archives > Technologies > Archives > PL/SQL > PL_FPDF

PL_FPDF

Free PL/SQL library to generate PDF from Oracle Database

Monday 7 July 2008

All the versions of this article: [English] [français]

Français

The free PL/SQL library to generate PDF are very hard to find.
As I could not find one, I’ve written it from the well known PHP library FPDF which was written by Olivier Plathey.

I propose you the last release of the PL_FPDF library (0.9.3).
I already use it on our production site laclasse.com.

download it on github

This code gives you the same functions than FPDF, except that it converts all the images you need in PNG.

Required Environment

– Oracle en version 10g (10.2.0.1 and above).
– Installation ofOracle Web Tool Kit required (owa, htp and htf packages).
– OrdSys.OrdImage required (Oracle cartridge for images).
– Installation of package URIFactory required.

Changelog
– 0.9.2 -> 0.9.3 :

  • Added Sample on setHeaderProc and setFooterProc procedures.
  • Added parameter implementation to thes procedures.
  • Modify Header and footer procedure behaviour to get parameter values
  • declared subtype ’word’ ans type ’tv4000a’ in the specs.

– 0.9.1 -> 0.9.2 :

  • Added procedure helloword Example.
  • Added procedure testImg Example.

– 0.9.1 First Stable Version !
- Minor bugs (thanks to Josh Burroughs)

  • Corrections on p_parseimage procedure (better reading of image files)
  • Corrections on p_escape procedure

– 0.9-Beta2
- Minor bugs (thanks to Rudy Winter)

  • Corrections on Image procedure
  • Corrections on header and Footer procedures

- Multiple images on the same page works, but only with png Format.

Known bugs on this version

– GIF images are not supported, because of a PNG conversion problem.

– No PDF file compression. (This not a bug but a lack of feature).
– Write procedure which allows to put a flow of text dealing with the page height and width, don’t run as well.
– ...

Asked Features

– Dynamique SQL execution procedure that generates aPDF table.

Download, Test, and code !

I’m very interessted in having back your codes, bugs corrections and evolutions.

code examples

Here are some online examples :

hello world

Test with an image

Here are some simple code samples with PL_FPDF.

helloWord
Note that the "output" procedure calls automatically "ClosePdf".

procedure helloworld is
begin
	pdf.FPDF('P','cm','A4');
	pdf.openpdf;
	pdf.AddPage();
	pdf.SetFont('Arial','B',16);
	pdf.Cell(0,1.2,'Hello World',0,1,'C');
       pdf.Output();
end helloworld;

Put an image

procedure testImg is
  img varchar2(2000);
begin
	pdf.FPDF('P','cm','A4');
	pdf.openpdf;
	pdf.AddPage();
	pdf.SetFont('Arial','B',16);
       img := 'http://www.laclasse.com/v2/images/picto_laclassev2.png'; 
       pdf.Image(img,1, 1, 10);
       pdf.Output();
end testImg;

Forum posts

  • Great work.

    I dont know if its limitation or I am having problems. Like can not put image, document size limit to 32k etc. i.e not usable for bigger complex/reports.

    Do you have any updated version?

    Regards
    Kishor

    • I think this is a limitation due to a PlSQL variable that is no more longer than 32K, I have to chck the types of the record cotaining the images.

      no update planned for august, I think I will work on it in September/October ?

      Best regards.

    • Hi,
      I had a look about the problem. I confirm than you can put big images in pdf (more than 32K).
      see example below.

      procedure testImageMoreThan32K is
begin
	pl_fpdf.FPDF('P','cm','A4');
	pl_fpdf.openpdf;
	pl_fpdf.AddPage();
	pl_fpdf.SetFont('Arial','B',12);
    pl_fpdf.Image('http://www.laclasse.com/v2/images/b2i/fonds/fd_livret_college.png' ,1, 1, 10);
	pl_fpdf.Output();
end testImageMoreThan32K;

      Perhaps you have problem in the configuration ? in the requiered environnement ?
      Let me now which error message you get...

    • Hi,

      Thanks for your reponse.
      Without any images, it creates the PDF and is displayed fine.

      However I am still getting the same error when I try to display an image.
      ie HTTP-404 The webpage can not be found.
      When I do the following from explorer
      http://myserver/i/reddot.gif
      the image is displayed.
      But with
      pl_fpdf.Image(’http://myserver/i/reddot.gif’ ,1, 1, 10);
      the page gives "The webpage can not be displayed".

      Is there any configuration changes required?
      have also tried
      pl_fpdf.Image(’http://www.laclasse.com/v2/images/b2i/fonds/fd_livret_college.png’ ,1, 1, 10);

      Appreciate your advise.

      Kishor

    • Hi,

      Now I am getting Adobe Reader’s ERROR: Wrong Operand Type.

      In p_parseImage its getting Ora-1403 no data found.

      The error is from line dbms_lob.read(pBlob, pLength, pHandle, l_data_raw);
      Looks like pBlob is empty.

      Any ideas?
      Thanks
      Kishor

    • Yes, I think it is because p_parseImage can’t find any image.
      It seems that your UrlFactory don’t access the the image you want to integrate ?

      Do you have any filtering between your Oracle DB server and the web server on which you want to get the image ?

    • Thanks for your advise.
      My web server and DB server is same.
      Instead of the server name, I used IP address and the following is happening.

      I did debug and confirm the file has been accessed in URIfactory as it identified the File format as PNGF.

      Instead of opening Acrobat reader, the message "File Download" box is appearing.

      When I save the file (saved as .pdf) and try to open it, Acrobat Reader is giving message:
      "There was an error opening this document. The File is damaged and could not be repaired."

      Also, the AcroRd32.exe process remains loaded and has to be killed as it hangs.

      If you need I can send you the generated PDF file. Please send me your email address.

      Appreciate your advise. Thanks a lot for you time.

      Kishor

    • Hi Kishor

      This seems to be a problem in png conversion with the Oracle OrdImage Cartridge.

      In PL_FPDF code ("getImageFromUrl" function), there is "on the fly" png conversion for all the images that are not in png format. But this seems not work with GIF format. And this format isn’t supported in the pdf.

      Perhaps this is because the GIF files are interpreted as PNG by Oracle OrdImage cartridge ?
      So the following test (in "getImageFromUrl" function) doesn’t pass ?

      	 if (myImg.getFileFormat() != 'PNGF' ) then
	 	myImg.process('fileFormat=PNGF,contentFormat=8bitlutrgb');
		myImg.setProperties();
	 end if;

      Perhaps this test matches with GIF Images ???
      I have to digg in it...

      HTH,
      Pierre-Gilles.

    • Im receiving the below error when executing the hello world program, please help me

      Cause: FDPSTP failed due to ORA-20100: PL_FPDF error: Output : ORA-06502: PL/SQL: numeric or value error
      ORA-06512: at "APPS.XXEQ_PDF", line 1704
      ORA-06512: at "APPS.XXEQ_PDF", line 3015
      ORA-06512: at

    • it seems you have a problem in APPS.XXEQ_PDF procedure, no ?
      The result of this error throws an error to PL_FPDF Output procedure.

    • Bon Jour Pierre,

      First of all thanks for sharing this package.
      Regarding the PNG header check, I encountered some issues too.
      It seems that the content of the "png_signature" variable is different interpreted in a multi-byte character database.
      Maybe a "raw" datatype should be considered for these actions.
      These are the results:

      PARAMETER                 VALUE
------------------------- --------------
NLS_CHARACTERSET          WE8ISO8859P1
NLS_NCHAR_CHARACTERSET    AL16UTF16


select dump(chr(137) || 'PNG' || chr(13) || chr(10) || chr(26) || chr(10)) from dual;

DUMP(CHR(137)||'PNG'||CHR(13)||CHR(10
-------------------------------------
Typ=1 Len=8: 137,80,78,71,13,10,26,10


PARAMETER                 VALUE
------------------------- --------------
NLS_CHARACTERSET          AL32UTF8
NLS_NCHAR_CHARACTERSET    AL16UTF16

select dump(chr(137) || 'PNG' || chr(13) || chr(10) || chr(26) || chr(10)) from dual;

DUMP(CHR(137)||'PNG'||CHR(13)||CH
---------------------------------
Typ=1 Len=7: 80,78,71,13,10,26,10

      By the way the setting of parameter "set nls_length_semantics" to BYTE or CHAR doesn’t change the results.

      I hope this helps (a bit ;-).

      JB

    • A short investigation in multibyte (unicode) characters reveals that for example in UTF-8 the first byte contains the (7 bits) US ASCII.
      Above the 127 values another byte will be enabled, this explains the "strange" behavior of the length function for a varchar2 string in a multibyte environment.

      HTH

      JB

    • I have written procedure using pl_fpdf that writes db content to pdf. It works very week on oracle 10g. However, same procedure not working on oracle 11g. The image function hangs the entire program. Is there known issues between image function of pl_fpdf and 11g? If so, is there any workaround for it?

      Thanks,
      Poli

  • Hi,

    I need to generate pdf from sql query,
    please suggest how can i use/modify PL_FPDF to do this.

    thanks& regards,
    krs

  • this Version of FPDF is a great piece of work! Thanks a lot.

  • Looks Great! I was wondering, was there a disadvantage to table loading some of the things like fonts and pagesizes or was this meant to be as close a port as possible and/or single package installation?

    • I did not us table loading for the fonts, because I wanted to keep a single package installation without needing to install a repository (i.E. tables end data).

      This a choice. This means that all data for font sizing are in the package and so the package is bigger and less comprehensive, but this keeps the installation simple.

      Regards,

  • Hi Pierre-Gilles

     

    The PDF documents produced by the 0.9.1 version of the package read by some PDF readers give give the errors like those:

     

    Error: Kids object (page 2) is wrong type (null)

    Error: Page count in top-level pages object is incorrect

    Error: Couldn’t read page catalog

     

    The bug can be resolved changing in the procedure p_putpages the lines

     

    kids := ’/Kids [’;

    for i in 0..nb loop

     

    with the following:

     

    kids := ’/Kids [’;

    for i in 0..nb-1 loop

     

    Alexandre

  • Bonjour!

    I tried your terrific solution, but I experienced some problems with your examples. It would be really great, if you can help me out!

    By trying the "Hello-World"-Example, I get the following Errormessage:

    ORA-20100: PL_FPDF error: Output : ORA-06502: PL/SQL: numeric or value error
    ORA-06512: in "TEST.PL_FPDF", Line 1916
    ORA-06512: in "TEST.PL_FPDF", Line 3252
    ORA-06512: in Line 7

    Line 7 is the line: pl_fpdf.Cell(0,1.2,’Hello World’,0,1,’C’);

    I checked the procedure’s paramters in the package, I can’t find the problem.

    I also tried "pl_fpdf.test();", but there was the same mistake in Line 2, which is the .test()-Line.

    Thank you very much for your help in advance!

    Au revoir,
    Thorben

  • Hi,

    I am using your excellent package for generating PDF files from PL/SQL, but I have found two problems (one typo and one bug):

    1) The error message in the "Cell" procedure incorrectly says error(’MultiCell : ’||sqlerrm), this should be error(’Cell : ’||sqlerrm);

    2) The p_out procedure fails with ORA-06502: PL/SQL: numeric or value error: character string buffer too small when building a semi-complex tabular report. I assume this is because the combined length of the pstr parameter and the existing data exceeds 32k. You need to add logic in the p_out procedure that will handle more data by breaking up the strings and adding more rows to the internal index-by-tables.

    Thanks!

  • Hi, my name is Fabio
    I am using your great package but i have a very big problem. When i try to load an image i receive always the ’The file is damaged and could not be repaired’ error. I’ve tried laso with the ’ ’http://www.laclasse.com/v2/images/picto_laclassev2.png’ file. The generated code is the following :

    %PDF-1.3
    3 0 obj
    <</Type /Page
    /Parent 1 0 R
    /Resources 2 0 R
    /Contents 4 0 R>>
    endobj
    4 0 obj
    <>
    stream
    2 J
    0.56 w
    BT /F1 12.0 Tf ET
    BT /F2 16.0 Tf ET
    q 283.46 0 0 53.14 28.34 513.78 cm /I1 Do Q

    endstream
    endobj
    1 0 obj
    <</Type /Pages
    /Kids [3 0 R ]
    /Count 1
    /MediaBox [0 0 841.89 595.28]
    >>
    endobj
    5 0 obj
    <</Type /Font
    /BaseFont /Helvetica
    /Subtype /Type1
    /Encoding /WinAnsiEncoding
    >>
    endobj
    6 0 obj
    <</Type /Font
    /BaseFont /Helvetica-Bold
    /Subtype /Type1
    /Encoding /WinAnsiEncoding
    >>
    endobj
    7 0 obj
    <</Type /XObject
    /Subtype /Image
    /Width 80
    /Height 15
    /ColorSpace /DeviceRGB
    /BitsPerComponent 8
    /Filter /FlateDecode
    /DecodeParms <

    >
    /Length >>
    stream
    endstream
    endobj
    2 0 obj
    <<
    /ProcSet [/PDF /Text /ImageB /ImageC /ImageI]
    /Font <<
    /F1 5 0 R
    /F2 6 0 R
    >>
    /XObject <<
    /I1 7 0 R
    >>
    >>
    endobj
    8 0 obj
    <<
    /Producer (PL_FPDF 0.9.2 portage pour Laclasse.com par P.G. Levallois de la version 1.53 de PHP/FPDF d’Olivier Plathey.)
    /CreationDate (D:20100219121453)
    >>
    endobj
    9 0 obj
    <<
    /Type /Catalog
    /Pages 1 0 R
    /OpenAction [3 0 R /FitH null]
    /PageLayout /OneColumn
    >>
    endobj
    xref
    0 10
    0000000000 65535 f
    0000001878 00000 n
    0000002397 00000 n
    0000001661 00000 n
    0000001739 00000 n
    0000001965 00000 n
    0000002061 00000 n
    0000002162 00000 n
    0000002521 00000 n
    0000002696 00000 n
    trailer
    <<
    /Size 10
    /Root 9 0 R
    /Info 8 0 R
    >>
    startxref
    2799
    %%EOF

    please help me!!!
    thanks

  • Hello. How I can put a header on all pages of the pdf file. I have the parameter SetAutoPageBreak set to true. Can you help me with an example of the procedure in the header. Thanks for the reply.

    • Hi,
      There is a proc that allows you to bind a hook for your header procedure :

      pl_fpdf.setheaderproc(’Mypackage.YourHeaderHook’);

      In the 0.9.3 version I added sample code that shows how to use this proc. Note that ’YourHeaderHook’ must have public specs in the ’Mypackage’ plsql package.

      The code below is copied from the 0.9.3 new version.

      procedure MyRepetitiveHeader(param1 varchar2, param2 varchar2) is
begin
    SetFont('Arial','I',9);
    cell(0,0,'Repetitive Header param1='||param1||', param2='||param2,0,1,'C');
    line(1,1.2,20,1.2);
end MyRepetitiveHeader;



procedure testHeader is
 img varchar2(2000);
 tHdr tv4000a; -- This is a table for the custom header proc hooked
begin
    -- setting parameter values for proc 'MyRepetitiveHeader'
    tHdr('param1') := 'Value for Param1';
    tHdr('param2') := '123456';
    
    setHeaderProc('pl_fpdf.MyRepetitiveHeader', tHdr);
    --setFooterProc('pl_fpdf.MyRepetitiveFooter');
    FPDF('P','cm','A4');
    openpdf;
    -- first page
    AddPage();   
    SetFont('Arial','B',16);
    Cell(0,1.2,'This is the first Page (left alignment)',0,1,'L');
    -- Second page
    AddPage();
    SetFont('Arial','B',16);
    Cell(0,1.2,'This is the second Page (right alignment)',0,1,'R');
    -- third page
    AddPage();
    SetFont('Arial','B',16);
    Cell(0,1.2,'This is the second Page (centered)',0,1,'C');
    Output();
end testHeader;

      sincerly.

      HTH.

    • Thanks for the help

  • Hi,
    I try to use your package, but I’ve encountered two basic problems. I want to make a simple print of employees in the table from the Oracle database with SQL.Please help with the following problems.
    1. If the employee name longer than the cells in the table, crossing the next cell in the tables.
    2.How to print the table header on each page.

    Please help. Thanks.

  • Hallo Pierre-Gilles,

    I use your version of FDPF to enable students to ask for exemption for certain courses. I let them upload proof of passing comparable courses. But then I come across a limitation: I can get only one (1) link per page to work. And sometimes they have several documents (grade lists, diplomes etc)
    Can you give a hint on how to enhance the link (url)possibilities in PL_FPDF?

    Kind regards,

    Henk Op den Brouw
    Tilburg University.

Any message or comments?

Forum registration required

You must be registered before participating in this forum. Please enter your personal identifier . If you have not yet registered, you must register.

Connection | Sign up | password forgotten?