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.
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
Hi Zeeshan,
Thanks.
1. You may need to write a custom function in PL/SQL library to obtain current block name, if you have such requirement.
2. The easiest way is to generate report is from report builder, but you can generate it from forms also. Check this link https://foxinfotech.in/2015/03/freebie-utility-form-generate-excel-report-from-sql-query-in-oracle-forms-6i-and-11g.html
Thanks.
thanks for reply
i am already use this https://foxinfotech.in/2015/03/freebie-utility-form-generate-excel-report-from-sql-query-in-oracle-forms-6i-and-11g.html
this is correct working ,this is form to excel
but i want button on report ,when i press button then report data convert into excel
is this possible ?
Thanks
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.
hi
VINISH
i am waiting for CODE u provide me as above conversation
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
No, there is no such option in this package to add password protection.
Yes i know, It would be better if you could add that option.
Thanks
This package is originally created by Anton Scheffer, see his website http://technology.amis.nl
Open this website and try to search for PDF. You will find some more new packages to generate PDF files.
Try them.
NO Sir,
Didn't find code for PDF generation with password.
Can you please provide the code.
Thanks.
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
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.
Use pdf_builder_pkg.new_page procedure to add a new page to PDF.
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.
Many Thanks
Satya
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:
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?
i do above procedure but i found an error in package.. pdf_builder_pkg.init;
pls give the solution
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
I am also facing the same issue. Can you please let me know, if you solved it already.
Thanks