ACCUEIL >>  Technologies et Territoires >>  Laboratoire du libre >>  PL/SQL
 

PL_FPDF

Free PL/SQL library to generate PDF from Oracle Database

Monday 7 July 2008 , par Pierre-Gilles Levallois
 

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.2). I already use it on our production site laclasse.com.

Zip - 26.7 kb
Latest stable version of pl_fpdf
This is the latest stable version of pl_fpdf : 0.9.2

previous releases

Zip - 26.4 kb
pl_fpdf : 0.9.1
Version of pl_fpdf : 0.9.1

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.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;

 



Répondre à cet article




PL_FPDF
1 December 2009, par TheHunger4More

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!

 

PL_FPDF
26 November 2009, par Thorben

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

 

PL_FPDF Kids object bug
9 October 2009, par arodichevski

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

 

PL_FPDF
20 July 2009, par rwendel

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?

    PL_FPDF
    11 August 2009, par Pierre-Gilles Levallois

    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,

 

PL_FPDF
25 May 2009, par Gobblin

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

 

PL_FPDF
5 September 2008, par krs

Hi,

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

thanks& regards, krs

    PL_FPDF
    10 September 2008, par krs

    hi,

    Is there any way to get the sql query output to pdf file.

    thnaks krs

 

PL_FPDF
11 August 2008, par kishor
Pl_FDPF

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

    PL_FPDF
    11 August 2008, par Pierre-Gilles Levallois

    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.

    PL_FPDF
    14 August 2008, par Pierre-Gilles Levallois

    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...

      PL_FPDF
      15 September 2008, par kishor

      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/b...’ ,1, 1, 10);

      Appreciate your advise.

      Kishor

        PL_FPDF
        17 September 2008, par 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

          PL_FPDF
          17 September 2008, par Pierre-Gilles Levallois

          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 ?

            PL_FPDF
            19 September 2008, par kishor

            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

              PL_FPDF
              2 October 2008, par Pierre-Gilles Levallois

              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.

                PL_FPDF
                5 November 2008, par jbkoyen

                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

                  PL_FPDF
                  7 November 2008, par jbkoyen

                  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

        PL_FPDF
        24 November 2009, par jackiboa

        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. Assignment | Coursework | Dissertation

      PL_FPDF

      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