Getting started with PL/PDF in Oracle Application Express

PL/PDF (www.plpdf.com) is a PL/SQL library for Oracle, which allows you to generate PDF documents.

You can use these libraries from within Apex to generate PDF reports, too. Here are a few simple steps on how to get it going.

1. Download PL/PDF library
2. Create a new schema on your database (maybe something like “tools”).
3. Install PL/PDF into the new schema (see PL/PDF installation guide).
4. Grant execute privileges on the PL/PDF packages to the Apex parsing schema. (In my case the parsing schema is SCOTT. For this demo, SCOTT only needs execute privileges on the PLPDF package.)
5. Create synonyms for the PL/PDF packages (again in this case only for the PLPDF package).
6. Grant execute on OWA_UTIL to the parsing schema (SCOTT).
7. In the parsing schema, create a procedure to generate the PDF document by means of the PL/PDF library (see procedure PRINT_PDF) below.
8. Create two pages in Apex:
   a. Page 200 with a text field (P200_TEXT), a button (P200_RUN_REPORT) that submits the page, and a branch that goes to page 201 when the submit button is clicked.

   b. Page 201 with a call to PRINT_PDF in a before header process (page 201).
   

   

9. Run page 200
10. Enter some text into the text field and click the button to submit the page.

Now the PDF document with the text entered on page 200 should show up.

[source language=”sql”]
create or replace
PROCEDURE PRINT_PDF (p_text in varchar2)
IS
l_blob BLOB;
l_mime_type VARCHAR2(255) := ‘application/pdf’;
BEGIN

Plpdf.init;
Plpdf.NewPage;
Plpdf.SetPrintFont(‘Arial’,NULL,12);
Plpdf.PrintCell(50,10,p_text);
Plpdf.SendDoc(l_blob);

owa_util.mime_header(l_mime_type, FALSE) ;
htp.p(‘Content-Disposition: filename="sample.pdf"’) ;
htp.p(‘Content-Length: ‘ || dbms_lob.getlength(l_blob)) ;
owa_util.http_header_close;
wpg_docload.download_file(l_blob);

END;
[/source]