PDF documents are often already stored inside Oracle Database as BLOB values: generated reports, invoices, archived documents, attachments, or documents received from external systems.
When several of these PDFs need to become one document, a typical application architecture moves the files outside the database, invokes an external PDF library or service, and then stores the resulting PDF back in Oracle.
PL/MERGE exposes the merge operation directly as a PL/SQL API. The inputs are PDF BLOBs and the result is another PDF BLOB, so the complete operation can remain part of the Oracle application workflow.
PL/MERGE supports three main usage patterns:
MERGE2for exactly two PDFs;MERGEwhen all source PDFs are available as a collection;- a stateful builder API when PDFs are received or loaded one at a time.
The actual merge operates on complete PDF pages. It does not regenerate their page content. Instead, pages and the PDF object graphs required to render them are imported into the resulting document.
Merging two PDF BLOBs
The simplest case is merging two existing PDFs.
declare
l_pdf1 blob;
l_pdf2 blob;
l_merged blob;
begin
-- l_pdf1 and l_pdf2 contain complete PDF documents.
l_merged := pl_merge.merge2(
p_pdf1 => l_pdf1,
p_pdf2 => l_pdf2
);
-- Persist or return l_merged as required by the application.
if dbms_lob.istemporary(l_merged) = 1 then
dbms_lob.freetemporary(l_merged);
end if;
end;
/
p_pdf1 is not simply the first document in page order. It is also the master PDF.
All pages from p_pdf1 appear first in the result, followed by all pages from p_pdf2.
The master also determines most document-level properties of the merged PDF, which becomes important when the source documents contain metadata, bookmarks, page labels, name trees, or other Catalog-level information.
Merging several PDFs
When the complete set of source PDFs is already available, PL_MERGE.MERGE accepts a t_pdf_list collection.
declare
l_pdf1 blob;
l_pdf2 blob;
l_pdf3 blob;
l_pdfs pl_merge.t_pdf_list := pl_merge.t_pdf_list();
l_merged blob;
begin
l_pdfs.extend(3);
l_pdfs(1) := l_pdf1; -- master
l_pdfs(2) := l_pdf2;
l_pdfs(3) := l_pdf3;
l_merged := pl_merge.merge(l_pdfs);
-- Persist or return l_merged.
end;
/
Page order follows collection order:
PDF 1 pages
PDF 2 pages
PDF 3 pages
...
The first element is again the master document.
There is one important collection requirement: t_pdf_list must be a dense, 1-based collection containing at least two PDFs.
For example, a collection containing elements 1, 2, and 4, but no element 3, is invalid.
NULL, empty, one-element, sparse, or otherwise invalid collections are rejected with ORA-20850.
When PDFs arrive one at a time
A collection is convenient when all input PDFs are already available. That is not always how database applications work.
An application may fetch documents sequentially, process a query row by row, or receive PDFs from multiple application operations.
For these cases PL/MERGE provides a stateful builder API:
declare
l_pdf1 blob;
l_pdf2 blob;
l_pdf3 blob;
l_merge pl_merge.t_merge_handle;
l_merged blob;
begin
l_merge := pl_merge.begin_merge(l_pdf1);
pl_merge.append(
p_merge => l_merge,
p_pdf => l_pdf2
);
pl_merge.append(
p_merge => l_merge,
p_pdf => l_pdf3
);
l_merged := pl_merge.finish(l_merge);
-- l_merge is NULL here.
exception
when others then
pl_merge.close_(l_merge);
raise;
end;
/
The lifecycle is:
BEGIN_MERGE
|
+-- APPEND
|
+-- APPEND
|
+-- ...
|
FINISH
BEGIN_MERGE creates the target document using the first PDF as master.
Each successful APPEND imports another PDF into that target.
FINISH performs the final PDF serialization, returns the resulting BLOB, releases the internal merge handle, and sets the handle to NULL.
At least one successful APPEND is required before FINISH.
If processing must be abandoned, CLOSE_ releases the builder without producing a PDF:
pl_merge.close_(l_merge);
Calling CLOSE_ with a NULL handle is safe, which makes it suitable for exception cleanup.
Why the first PDF matters
PDF merging is more complicated than concatenating page streams.
A PDF contains both page-level objects and document-level structures. If two input documents contain different metadata, bookmarks, name trees, page labels, or viewer configuration, there is no universally correct way to combine all of them.
PL/MERGE therefore uses a deterministic first-document policy.
The first PDF supplies the retained document-level state, including:
| Document feature | Merge policy |
|---|---|
| Catalog | Master PDF |
| Info dictionary | Master PDF |
| XMP metadata | Master PDF |
| Names | Master PDF only |
| Outlines / bookmarks | Master PDF only |
| PageLabels | Master PDF only |
| Threads | Master PDF only |
| PDF version | Maximum effective input version |
Later documents primarily contribute their pages and the resources required to render those pages.
This means the choice of the first document should be deliberate.
If one PDF contains the metadata, bookmarks, page labels, or other document-level configuration that should survive the merge, use that PDF as p_pdf1, p_pdfs(1), or the argument to BEGIN_MERGE.
What happens to imported pages?
An imported PDF page is not just its /Contents stream.
A page can depend on inherited page-tree properties and on many indirect PDF objects. PL/MERGE normalizes the page before import so it can function correctly inside the new page tree.
Where required, inherited properties such as:
/Resources
/MediaBox
/CropBox
/Rotate
are materialized directly on the imported page.
The merge path also handles page dependencies such as:
- fonts;
- images and other XObjects;
- annotations;
- transparency
/Groupdictionaries; - resource dictionaries;
- single or multiple
/Contentsstreams.
Shared resources are preserved as shared objects where applicable rather than being independently cloned for every page.
This is an important distinction between a PDF-aware merge and simply manipulating BLOB contents: the object relationships required by each imported page must remain valid in the resulting PDF.
One final PDF rewrite
PL/MERGE performs one final full save of the resulting PDF.
It does not produce the merge as an incremental PDF update.
For a collection merge, all source documents are imported first and the full PDF is serialized once after the final source.
The builder behaves similarly: individual APPEND operations import the source into the target, while the final full save occurs during FINISH.
The result is a new caller-owned PDF BLOB.
BLOB ownership
BLOB lifecycle matters in PL/SQL applications, particularly when temporary LOBs are involved.
PL/MERGE does not take ownership of the input BLOBs supplied by the caller.
For MERGE2 and MERGE:
- all input BLOBs remain caller-owned;
- PL/MERGE does not free them;
- the returned merged BLOB is caller-owned.
The builder has a slightly different lifetime rule.
The first BLOB passed to BEGIN_MERGE must remain valid until FINISH or CLOSE_, because the live target may still lazily reference it.
An appended source has a shorter required lifetime. After:
pl_merge.append(
p_merge => l_merge,
p_pdf => l_source
);
returns successfully, the source has been detached into the target and the caller may release that source BLOB.
This makes the builder API useful when a large number of PDFs are processed sequentially and the application does not want to retain every source BLOB until the entire merge has completed.
Signed and encrypted PDFs
PL/MERGE intentionally rejects encrypted PDFs.
It also rejects PDFs that already contain digital signatures.
The reason for the signature restriction follows directly from the processing model: PL/MERGE performs a full rewrite, and rewriting a signed PDF would invalidate the existing signature.
The correct processing order is therefore:
Source PDFs
|
v
PL/MERGE
|
v
Merged PDF
|
+--> Digital signature
|
+--> Encryption
In other words:
merge first, then sign or encrypt the final document.
PDF forms and tagged PDFs
The current merge policy does not merge AcroForm structures. PDFs containing an AcroForm are therefore rejected.
Tagged PDFs also require special handling because their logical structure tree describes relationships between document content and semantic structure.
A tagged PDF cannot currently be used as the master document.
A tagged appended PDF can be imported visually, but its source structural tagging is removed during the merge.
PL/MERGE therefore merges the rendered pages, not the source document’s logical tagging structure.
Handling merge failures
PL/MERGE propagates PDF parser and processing errors rather than silently ignoring them.
Some useful facade-level errors are:
ORA-20850
Invalid MERGE collection.
ORA-20851
Invalid or stale builder handle.
ORA-20852
Invalid builder state, for example FINISH before APPEND.
ORA-20702
NULL, empty, or invalid PDF source.
ORA-20705
Malformed or unsupported PDF structure.
ORA-20707
Encrypted PDF.
ORA-20719
Existing digital signature.
Applications should preserve the complete Oracle error message and backtrace when logging failures, because lower-level PDF processing errors may also be propagated.
The builder has an additional useful property: a failed APPEND is failure-atomic with respect to the target.
If one source cannot be appended, that source is not committed to the builder. The builder remains valid and the application can decide whether to try another source.
A failure during FINISH, however, is terminal: the builder is discarded and its handle becomes NULL.
Choosing the appropriate API
The three interfaces are intended for different application patterns.
Use:
MERGE2
when exactly two complete PDFs are already available.
Use:
MERGE
when the complete ordered collection of PDFs is available before processing starts.
Use:
BEGIN_MERGE / APPEND / FINISH
when documents are obtained sequentially or when individual source BLOBs should be released as soon as they have been imported.
All three produce the same basic result: complete pages from several source PDFs combined into one new PDF BLOB, with the first document defining the retained document-level state.
A database-native PDF workflow
For an Oracle application, the important part of this model is that the PDF operation fits naturally into existing PL/SQL processing.
The application starts with PDF BLOBs:
BLOB + BLOB + BLOB
performs the merge through the PL_MERGE package:
l_merged := pl_merge.merge(l_pdfs);
and receives another BLOB:
Merged PDF BLOB
That output can then continue through the application’s normal storage, delivery, signing, or other document-processing workflow.
PL/MERGE is designed specifically for this task: combining complete existing PDF documents inside an Oracle PL/SQL application while preserving the page-level PDF object graphs required to render the imported pages correctly.
