Merge/Combine Multiple PDF Files into One PDF in Oracle Using PLPDF_TOOLKIT PL/SQL Package

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.

See also:

• Display BLOB Contents (PDF, Images) into a Region in Oracle Apex Page
• How to Create PDF Report Using PL/SQL