Merging multiple PDFs into single PDF
You can merge multiple PDFs into a single PDF using AOP. In aop_api_pkg there is a parameter called g_output_merge which can be set to true to merge the output of multiple requests into a single PDF.
- SQL Option
- JSON Option
aop_api_pkg.g_output_merge := 'true';
{
"output": {
"output_merge": true
}
}
Merging Multiple Requests into Single PDF
In some situations when our PDF is too large you can split the PDF into multiple requests, and merge the output of all the requests into a single PDF File.
Suppose you have reports of 5 customers, and you want to merge the output of all the requests into a single PDF. This technique is interesting when you want to generate very big documents. By cutting them in smaller documents and merging them later, might be faster in certain cases.
At first, lets send the request to AOP to generate the PDFs for all the customers and add it to a collection called AOP_MERGE. You will be sending multiple requests using loop.
DECLARE
l_collection_name VARCHAR2(100) := 'AOP_MERGE';
l_binds wwv_flow_plugin_util.t_bind_list;
l_return BLOB;
l_output_filename VARCHAR2(100) := 'output';
l_files_sql VARCHAR2(4000);
-- change the following variables in case you run from PL/SQL
l_app_id NUMBER := :app_id;
l_page_id NUMBER := :app_page_id;
l_username VARCHAR2(200) := :app_user;
l_aop_url VARCHAR2(500) := apex_app_setting.get_value('AOP_URL');
l_api_key VARCHAR2(100) := apex_app_setting.get_value('AOP_API_KEY');
BEGIN
FOR i IN 1..5 LOOP
-- define bind variables
l_binds(1).name := 'p_id';
l_binds(1).value := i;
-- call AOP per document
l_return := aop_api_pkg.plsql_call_to_aop(p_data_type => aop_api_pkg.c_source_type_sql, p_data_source => q'~
SELECT
'file1' AS "filename",
CURSOR (
SELECT
c.cust_first_name AS "cust_first_name",
c.cust_last_name AS "cust_last_name",
c.cust_city AS "cust_city",
CURSOR (
SELECT
o.order_total AS "order_total",
'Order ' || ROWNUM AS "order_name",
CURSOR (
SELECT
p.product_name AS "product_name",
i.quantity AS "quantity",
i.unit_price AS "unit_price",
apex_web_service.blob2clobbase64(p.product_image) AS "image"
FROM
aop_sample_order_items i,
aop_sample_product_info p
WHERE
o.order_id = i.order_id
AND i.product_id = p.product_id
) "product"
FROM
aop_sample_orders o
WHERE
c.customer_id = o.customer_id
) "orders"
FROM
aop_sample_customers c
WHERE
customer_id = :p_id
) AS "data"
FROM
dual
~',
p_template_type => aop_api_pkg.c_source_type_apex, p_template_source => 'aop_template_d01.docx',
p_output_type => 'pdf',
p_output_filename => l_output_filename, p_binds => l_binds, p_aop_url => l_aop_url, p_api_key => l_api_key,
p_app_id => l_app_id,
p_aop_remote_debug => 'No');
-- add to APEX collection
apex_collection.add_member(p_collection_name => l_collection_name, p_blob001 => l_return);
END LOOP;
Now, lets merge the documents stored in the collection into a single PDF, first select all the documents and call the AOP convert API to merge all files and create a single PDF.
-- prepare the SQL for the merge
-- This statement is selecting the 5 documents that have been stored in the collection
l_files_sql := q'~
select 'pdf'||to_char(seq_id) as filename, 'application/pdf' as mime_type, blob001 as file_blob
from apex_collections
where collection_name = 'AOP_MERGE'
order by seq_id
~';
-- call the AOP Convert API to merge all files and create a single PDF
l_return := aop_convert_pkg.convert_files(p_query => l_files_sql, p_output_type => 'pdf', p_output_filename => l_output_filename, p_aop_url => l_aop_url, p_api_key => l_api_key, p_app_id => l_app_id, p_aop_remote_debug => 'No');
-- insert the merged file in a table
INSERT INTO aop_output (
output_blob,
filename,
mime_type,
last_update_date
) VALUES (
l_return,
l_output_filename,
'application/pdf',
sysdate
);
END;
Merging different AOP DA calls
You can combine different DA calls and merge the outputs into a single PDF. Lets take an example which will be combining the output of two different DA calls and merge them into a single PDF.
At first, lets save the response of the two DA calls into a collection. Lets use the collection named SCREEN_CAPTURE. For that we will be sending the output to a procedure called save_collection.
Two DA calls generates reports of two different customers. AOP can merge its output by prepending the files present in the collection. For that, place the following SQL in INIT PL/SQL Code section.
aop_api_pkg.g_prepend_files_sql := q'[
select
c001 as filename,
c002 as mime_type,
blob001 as file_blob
from apex_collections
where collection_name = 'SCREEN_CAPTURE'
]';
It will generate a single PDF with the output of both the DA calls.
Merging different PDFs with Page numbers
AOP is able to merge different PDFs and add page numbers to each page. Lets take an example where we will be merging two PDFs and add page numbers to each page. Like before. we'll be combining the output of two DA calls and merge them into a single PDF. We will be using the collection named SCREEN_CAPTURE for this purpose.
At first, lets save the response of the two DA calls into a collection. Lets use the collection named SCREEN_CAPTURE. For that we will be sending the output to a procedure called save_collection.
You'll need to create a third Dynamic Action with the following Data Source:
SELECT
'file1' AS "filename",
CURSOR (
SELECT
CURSOR (
SELECT
CURSOR (
SELECT
'Page {currentpage}/{totalpage}' AS "text",
500 AS "x",
10 AS "y",
0 AS "rotation",
'false' AS "bold",
'false' AS "italic",
'Arial' AS "font",
'#000000' AS "font_color"
FROM
dual
) AS "all"
FROM
dual
) AS "aop_pdf_texts"
FROM
dual
) AS "data"
FROM
dual
This is the SQL for insertion of page numbers in each pages, for merging the PDF's we will be using the following SQL in INIT PL/SQL Code section.
aop_api_pkg.g_prepend_files_sql := q'[
select
c001 as filename,
c002 as mime_type,
blob001 as file_blob
from apex_collections
where collection_name = 'SCREEN_CAPTURE'
]';
template.docx