Blog

Reporting

PL/PDF – Code Reuse

When it comes to generating PDF reports, there are some options available. However, in the real world it is not just about generating simple PDFs, it is about being powerful and flexible enough to deliver our customer’s requirements. After several projects working with PL/PDF, I am going to share my experiences in a series of blogs.

In this first blog, the theme is code reuse.

When you generate a report package from a word template the PL/PDF engine generates a PL/SQL Package – for more information on this click here.

If you have a small application with a couple of reports, you can have a word template for each report, but if the system has a considerable number of reports then it is more productive to share some code between them. One good example is the report header (i.e a generic company branded letter-header); you do not need duplicate copies of that code within each report package. You can have just one procedure and call it from each report package. In the future, if you need to change the header of all reports, for example if the company branding were to change, then you just need to change that procedure.

To begin, we should understand what happen inside a report package. Below is a simple PL/SQL block to generate a PDF with “hello world” message and save to a table called BLOB_PLPDF.

Declare
   l_pdf_file blob;
Begin
   plpdf.init; /* initialise the PL/PDF engine */
   plpdf.newPage;  /* create a new page */
   plpdf.nonstrokingcolor(p_r => 64,
                          p_g => 64,
                          p_b => 64);
   plpdf.SetPrintFont(p_family => 'Helvetica',
                      p_style => null,
                      p_size => 12);
   plpdf.PrintoutText(p_x => 5,
                      p_y => 20,
                      p_txt => 'Hello World');
   plpdf.SendDoc( p_blob => l_pdf_file);  /* create a blob file */
   Insert into BLOB_PLPDF (PDF) values (l_pdf_file);
End;

All generated packages by the PL/PDF engine, includes those lines in bold. The code included between the plpdf.init and the plpdf.SendDoc will be printed to the PDF.

How can we reuse code in this scenario?

On the example below, we have some HR reports that uses the same Header and Sub header on all reports, only the body is different between them.

In this case, we do not need to include the first two sections (header and sub header) on each report template; we can create one template for the header (PR_PRINT_HEADER), one for the sub header (PR_SUB_HEADER) and call them inside each report (PR_EMPLOYEE_EXTRA_HOURS). See the example below.

…here is a quick example

…here is a quick example
create or replace procedure PR_PRINT_HEADER
AS
  l_pdf_file blob;
Begin
  /*plpdf.init;  
  plpdf.newPage; */ 
  plpdf.nonstrokingcolor(p_r => 64,p_g => 64,p_b => 64);      
  plpdf.SetPrintFont(p_family => 'Helvetica', 
                                    p_style => null, 
                                    p_size => 12);  
  plpdf.PrintoutText(p_x => 5, p_y => 30, p_txt => 'I AM THE HEADER');   
  /*plpdf.SendDoc( p_blob => l_pdf_file);  
  Insert into BLOB_PLPDF (PDF) values (l_pdf_file);*/
End;
/
create or replace procedure PR_SUB_SUBHEADER
AS
  l_pdf_file blob;
Begin
  /*plpdf.init;  
  plpdf.newPage;*/ 
  plpdf.nonstrokingcolor(p_r => 184,p_g => 0,p_b => 64);      
  plpdf.SetPrintFont(p_family => 'Helvetica', 
                                    p_style => null, 
                                    p_size => 12);  
  plpdf.PrintoutText(p_x => 5, p_y => 35, p_txt => 'I AM THE SUB HEADER');   
  /*plpdf.SendDoc( p_blob => l_pdf_file);  
  /*Insert into BLOB_PLPDF (PDF) values (l_pdf_file);*/
End;
/

create or replace procedure PR_EMPLOYEE_EXTRA_HOURS
AS
  l_pdf_file blob;
Begin
  plpdf.init;  /* initialise the PL/PDF engine */
  plpdf.newPage;  /* create a new page */

  PR_PRINT_HEADER;
  PR_SUB_SUBHEADER;

  plpdf.nonstrokingcolor(p_r => 64,p_g => 64,p_b => 64);      
  plpdf.SetPrintFont(p_family => 'Helvetica', 
                                    p_style => null, 
                                    p_size => 12);  
  plpdf.PrintoutText(p_x => 5, p_y => 40, p_txt => 'I AM THE BODY');   
  plpdf.SendDoc( p_blob => l_pdf_file);  /* create a blob file */
Insert into BLOB_PLPDF (PDF) values (l_pdf_file);
End;
/

… and here is the PDF

In the future, if we need to change the header or sub header, as all report share the same procedure, we just need to edit in one place.

Rodrigo Mesquita Contributor
Oracle APEX Development Consultant , Explorer UK Ltd

Rodrigo is a Development Consultant at Explorer. Rodrigo has an Oracle APEX Developer Expert Certification issued by Oracle and is an experienced software engineer with emphasis in analysis, design and development of bespoke Oracle applications utilising development tools such as PL/SQL, APEX and Forms for a variety of international businesses.

follow me

Dive straight into the feedback!
Login below and you can start commenting using your own user instantly