1. Overview
This document talks about how to merge the pdf in AOP.
2. Technologies and Tools Used
The following technologies has been used to achieve exporting IR data to Word format.
- AOP
- PLSQL
3. Use Case
As we all know, APEX Office Print significantly simplifies printing and exporting (docx, xlsx, pptx, pdf, html, md, txt, csv, ics, and more) in Oracle Application Express (APEX) or simply in PL/SQL.
In some situations, you have 5 order reports that your customer asking to make it as a single PDF. In that case, there is a parameter called g_output_merge in aop_api_pkg that can be set to true to merge the output of many queries into a single PDF.
4. Architecture
At first, let’s 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, let’s merge the papers in the collection into a single PDF. First, select all of the documents and use the AOP convert API to merge all files and create a single PDF.
BEGIN
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
~’;
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;
Finally, we can download the merged pdf from the table aop_output.
