In this tutorial, I am giving an example to merge/combine multiple PDF files into one PDF file in Oracle using PLPDF_TOOLKIT PL/SQL package.
Assume you have a table for employees having a BLOB field and for each employee this table containing multiple documents in PDF format and you want to merge all these documents for an employee to one document and save it to another table’s BLOB field.
You can check my previous posts to how to save PDF files into a BLOB column, below is the list:
- How to Get BLOB from File in PL/SQL?
- How to Save BLOB as File in PL/SQL?
- How to Get File From BLOB in Oracle?
From the above-mentioned posts, you will learn how to save single PDF and extract the BLOB data containing single PDF. Here you will learn how to combine multiple PDF files to one PDF file and store in a BLOB.
Merge/Combine Multiple PDFs into One PDF File Using PLPDF_TOOLKIT.MERGE Function
The following is the simple structure of an employee table to contain multiple PDF files (in multiple records) stored in BLOB for each employee. From this table, we will get the files to merge. For testing, create this table and insert some multiple records (PDF files) for employee number 76465:
1. Create a Source Table
Create Table Emp_Docs ( empno number, blob_data blob );
2. Create a Table to Store Merged PDF
Now create a table to store the merged PDF files for an employee:
Create Table Emp_Pdfs ( empno number, merged_pdf blob );
3. Create a PL/SQL Program to Merge PDFs
Then run the following PL/SQL code to get the PDF files for an employee 76465, merge into one PDF file and store it to Emp_Pdfs table. To perform this task, I am using PLPDF_TOOLKIT.MERGE function.
Declare l_Blob1 BLOB; l_Blob2 BLOB; l_Blob3 BLOB; CURSOR Cur_empDocs IS SELECT blob_data, Rownum Row_n FROM emp_docs WHERE empno = 76465; n_Count INTEGER := 0; BEGIN FOR c IN Cur_empDocs LOOP IF c.row_n = 1 THEN Dbms_Lob.Createtemporary(Lob_Loc => l_Blob1, Cache => TRUE, Dur => Dbms_Lob.Call); l_Blob1 := c.blob_data; ELSE Dbms_Lob.Createtemporary(Lob_Loc => l_Blob2, Cache => TRUE, Dur => Dbms_Lob.Call); l_Blob2 := c.blob_data; Dbms_Lob.Createtemporary(Lob_Loc => l_Blob3, Cache => TRUE, Dur => Dbms_Lob.Call); l_Blob3 := Plpdf_Toolkit.Merge(l_Blob1, l_Blob2); l_Blob1 := l_Blob3; END IF; n_Count := n_Count + 1; END LOOP; IF n_Count > 0 THEN insert into emp_pdfs (empno, merged_pdf) values (76465, l_blob1); COMMIT; END IF; EXCEPTION WHEN OTHERS THEN raise; END;
For example, if there are 3 PDF files stored in 3 records for the employee 76465, it will be combined into one PDF. Even if there is only one PDF document for an employee then it will store the only one.
You can query the table Emp_Pdfs to see the result.