Home » PLSQL » How to Create PDF Report Using PL/SQL

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:

create pdf report using pl/sql
create a PDF report using PL/SQL

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:

19 thoughts on “How to Create PDF Report Using PL/SQL”

  1. HI Sir
    great work
    i have 2 questions
    1:in 6i when we run the form and press F5 they show the all Blocks (database block and control block) name of Form. How can i see Block name in 12c ?
    2: what is the easy way to convert report to excel in 12c
    thanks
    regards:
    Zeeshan

  2. you are welcome.
    you can use srw.run_report procedure to run the same report but set the output to excel. you can try it.
    if i will get time then will try to provide you the code for the same.
    thanks.

  3. Hi Sir,
    i've generated PDF with your reference doc. But now i need to encrypt password in the pdf generated file. can you please help me for the same.
    thanks in advance.
    Zee

  4. thank you for script but i this script does not support arabic language and right to left direction can you help me please and i will pay any money you want

  5. I use your Oracle Package to generate pdf report. The report should have multiple pages but only first page is generated. I try to use set_pagesize procedure, but it does not work.

  6. Satya Prakash Panigrahi

    Hi Vinish
    Thanks for your post. I was able to download and generate pdf pretty quick. I would like to understand 2 things from you.

    1. Is it possible to add a new font to the package and start using it in the pdf?
    2. Is it possible to increase/decrease the page margin of the pdf?

    Many Thanks
    Satya

    1. It is currently supporting only five fonts, helvetica, times, symbol, courier, and zapfdingbats.

      For margins, you can use the procedure set_margins. You can check the package specification for all procedures:

      procedure set_margins
          ( p_top in number := 3
          , p_left in number := 1
          , p_bottom in number := 4
          , p_right in number := 1
          , p_unit in varchar2 := 'cm'
          );
      
    2. Satya Prakash Panigrahi

      Thank you so much for your prompt reply. I tried modifying the set_margin procedure but it throws compilation error for any change. If I change the value from 1 to 2 it doesn't accept. Is there anything else I need to do?

  7. Hi,
    I have tested this package, and it works fine with Oracle 12c RDBMS.
    But it is hanging (get stuck) when the same PL/SQL code is executed against an Oracle 19c RDBMS database.
    Both daabase having similar CPU, memory (SGA, PGA) configurations.
    This can be checked by running the second piece of the demo in this blog.
    Does someone reproduce this issue? Is that maybe a bug in the original PL/SQL code?
    Any orher idea or suggestions?
    Kind Regards

Comments are closed.