How to Create PDF Report Using PL/SQL

Before writing and giving this example to create a PDF report using PL/SQL in Oracle, I researched a lot on Google, but I did not find any proper example. So I thought I should give an example to generate a PDF report file using PL/SQL. To do this first, you need to install pdf_builder_pkg database package in your schema. You can download this package from the following link pdf_builder_pkg.

After executing the above package script in your schema, you need to create a database directory object for your PDF files location, as shown below:

CREATE OR REPLACE DIRECTORY 
MY_PDF_DIR AS 
'D:\abc\plsql_pdfs'
/

Change the path in the above command as per your windows directory path or if you are using Linux or Unix then according to their location, as shown in below example:

CREATE OR REPLACE DIRECTORY 
MY_PDF_DIR AS 
'/usr09/oracle/files/'
/

You can change the directory name MY_PDF_DIR to any name because we need to pass this directory name as a parameter to a save_pdf procedure, which I will show you in the below example.

Now you have already installed the database package and created the directory object. Now you can write the procedure to create a PDF report using PL/SQL. Here below is the example PL/SQL block.

DECLARE
 CURSOR c_emp
 IS
 SELECT empno,
 ename,
 job,
 mgr,
 hiredate,
 sal,
 comm,
 deptno
 FROM employee;

v_hdr VARCHAR (1000);
 v_dtl VARCHAR (1000);
 v_rec NUMBER (10) := 0;
 v_sal NUMBER (10) := 0;
 v_comm NUMBER (10) := 0;
 BEGIN
 /* First line to initialize the package*/
 pdf_builder_pkg.init;
 /* Set the font to bold for heading*/
 pdf_builder_pkg.set_font ('helvetica', 'b');
 /* Write a line using pdf_builder_pkg.write procedure*/
 pdf_builder_pkg.write ('Employee Report');
 /* Set the font to normal */
 pdf_builder_pkg.set_font ('helvetica');
 pdf_builder_pkg.
 write ('Printed Date: ' || SYSDATE, -1, p_alignment => 'right');
 pdf_builder_pkg.
 write (
 '________________________________________________________________________________');
 pdf_builder_pkg.write (' ' || CHR (10) || CHR (13));
 /* Setting font courier for better alignment*/
 pdf_builder_pkg.set_font ('courier');
 v_hdr :=
 RPAD ('Emp No.', 10, ' ')
 || RPAD ('Emp Name', 12, ' ')
 || RPAD ('Job', 10, ' ')
 || RPAD ('Manager', 10, ' ')
 || RPAD ('Hire Date', 12, ' ')
 || RPAD ('Salary', 10, ' ')
 || RPAD ('Comm', 10, ' ');
 pdf_builder_pkg.write (v_hdr);
 pdf_builder_pkg.set_font ('helvetica');
 pdf_builder_pkg.
 write (
 '________________________________________________________________________________');
 pdf_builder_pkg.write (CHR (10) || CHR (13));
 pdf_builder_pkg.set_font ('courier');

FOR c IN c_emp
 LOOP
 v_dtl :=
 RPAD (c.empno, 10, ' ')
 || RPAD (c.ename, 12, ' ')
 || RPAD (c.job, 10, ' ')
 || RPAD (c.mgr, 10, ' ')
 || RPAD (c.hiredate, 12, ' ')
 || RPAD (c.sal, 10, ' ')
 || RPAD (c.comm, 10, ' ')
 || CHR (10)
 || CHR (13);
 pdf_builder_pkg.write (v_dtl);
 v_rec := v_rec + 1;
 v_sal := v_sal + c.sal;
 v_comm := v_comm + NVL (c.comm, 0);
 END LOOP;

pdf_builder_pkg.set_font ('helvetica');
 pdf_builder_pkg.
 write (
 '________________________________________________________________________________');
 pdf_builder_pkg.set_font ('courier');
 pdf_builder_pkg.write ('Records Count:' || v_rec);
 pdf_builder_pkg.write ('Total Salary:' || v_sal);
 pdf_builder_pkg.write ('Total Comm:' || v_comm);
 /* Saving the PDF file by passing directory name and file name */
 pdf_builder_pkg.save_pdf ('MY_PDF_DIR', 'emp_report.pdf');
 END;
 /

You can find the PDF file named emp_report.pdf at your directory location. If you want more command reference for package pdf_builder_pkg, you can check the following link to download. The PDF report will look like as shown below:

You can also check this utility created by me to generate PL/SQL procedure template as per your SQL statement. Please check Generate PL/SQL Procedure Template.

See also:

• Create Excel Files Using PL/SQL
• Create ZIP Files Using PL/SQL
• Export Data to JSON File in Oracle 11g Using PL/SQL