Append/Prepend Files
Introduction
Available From: v1.0AOP also allows a functionality that enables users to include other documents or images at the beginning or end of the AOP processed document. This can be achieved by specifying the files in the global variables g_prepend_files_sql
and g_append_files_sql
. All append/prepend files will first be converted to PDF, after which the pages (as a whole) will be prepended/appended to the output file. E.g. images will appear on their own page, not "pasted" into the template file.
When image is provided for appending or prepending, it will appear on its own page, not pasted into the template file.
When appending or prepending files, the supported output type is limited to PDF format only.
Mind that tags in append/prepend files will not be parsed! Append/prepend files are only converted and appended/prepended to the output files.
Since AOP 22.2, when image files are being converted using append/prepend, the orientation and page format for the appended/prepended file is same as the resolution of image. Providing g_output_page_format or g_output_page_orientation in output object overrides this features, and uses provided page format or orientation.
- Init PL/SQL
- JSON
g_output_page_format := 'letter' -- varchar2(10) := a4 (default) or letter
g_output_page_orientation := 'landscape' -- varchar2(10) := portrait (default) or landscape
"output": {
...
"page_format": "letter",
"page_orientation": "landscape"
}
Generally Appending and Prepending files gives an error when any file being appended/prepended is corrupt, which results in aop request to result an error. Since AOP 22.2, now we can specify a flag g_output_ignore_conversion_errors on the "output" JSON object, setting its value to true will ignore any errors and skip those files and append/prepend only valid files.
- Init PL/SQL
- JSON
g_output_ignore_conversion_errors := 'true'; -- boolean := false(default);
"output": {
...
"output_ignore_conversion_errors ": true
}
The order in which files are selected for appending or prepending is crucial, as AOP renders the output based on the file order selected.
Available aop_api_pkg variables for Prepend/Append files are listed here
Supported mime types for appending/prepending file.
File | MimeTypes |
---|---|
application/pdf | |
Text | text/plain |
CSV | text/csv |
Markdown | down text/markdown |
HTML | text/html |
Word | application/vnd.openxmlformats-officedocument.wordprocessingml.document |
Excel | application/vnd.openxmlformats-officedocument.spreadsheetml.sheet |
PowerPoint | application/vnd.openxmlformats-officedocument.presentationml.presentation |
JPEG | image/jpeg |
PNG | image/png |
GIF | image/gif |
BMP | image/bmp |
MS-BMP | image/x-ms-bmp |
EML | message/rfc822 |
MSG | application/vnd.ms-outlook |
HEIC | image/heic |
HEIC support added from AOP v24.2.2 onwards.
Supported File Sources for files
AOP allows various files sources for the append and prepend files which are as follows:
Demo for the usage of these file sources are given below.
Base64
An example showing base64 as the prepend file source:
- Init PL/SQL
- JSON
aop_api_pkg.g_append_files_sql := q'[select file_name as filename, mime_type, file_content as file_blob
from apex_application_static_files
where file_name = 'Sample_Append_File.pdf'
order by filename]';
"append_files": [
{
"name": "Sample_Append_File.pdf",
"mime_type": "application/pdf",
"file_content": "... base64ecoded of the file ...",
"file_source": "base64"
}
],
URL
An example showing url as the prepend file source:
- Init PL/SQL
- JSON
aop_api_pkg.g_prepend_files_sql := q'[select 'pre_file1.docx' as filename, 'application/vnd.openxmlformats-officedocument.wordprocessingml.document' as mime_type, 'https://www.getfile.com/pre_file1.docx' as url_call_from_aop from dual]';
"prepend_files": [
{
"filename": "pre_file1.docx",
"mime_type": "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
"file_source": "url",
"file_url": "https://www.getfile.com/pre_file1.docx"
}
]
File
An example showing file location in aop server as the prepend file source:
- Init PL/SQL
- JSON
aop_api_pkg.g_prepend_files_sql := q'[select 'pre_file1.docx' as filename, 'application/vnd.openxmlformats-officedocument.wordprocessingml.document' as mime_type, 'C:\Users\aopdesk\Downloads\pre_file1.docx' as file_on_aop_server from dual]';
"prepend_files": [
{
"filename": "C:\\Users\\aopdesk\\Downloads\\pre_file1.docx",
"mime_type": "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
"file_source": "file"
}
]
FTP
An example showing ftp as the prepend file source:
- Init PL/SQL
- JSON
aop_api_pkg.g_prepend_files_sql := q'[select 'pre_file1.docx' as filename, 'image/png' as mime_type, 'ftp://foo:pass@192.168.1.68:22/hello.png' as url_call_from_aop from dual]';
"prepend_files": [
{
"filename": "pre_file1.docx",
"mime_type": "image/png",
"file_source": "url", //or "ftp"
"file_url": "ftp://foo:pass@192.168.1.68:22/hello.png"
}
]
SFTP
An example showing sftp as the prepend file source:
- Init PL/SQL
- JSON
aop_api_pkg.g_prepend_files_sql := q'[select 'pre_file1.docx' as filename, 'image/png' as mime_type, 'sftp://foo:pass@192.168.1.68:22/hello.png' as url_call_from_aop from dual]';
"prepend_files": [
{
"filename": "pre_file1.docx",
"mime_type": "image/png",
"file_source": "url", // or "sftp"
"file_url": "sftp://foo:pass@192.168.1.68:22/hello.png"
}
]
Plain
An example showing plain text as the prepend file source:
"prepend_files": [
{
"mime_type": "text/plain",
"file_source": "plain",
"file_content": "Hello, this is the plain text sent as a prepend file."
}
]
Example for Append File
Data Source
Hereby examples of data source for different options.
- SQL
- PL/SQL returning SQL
- PL/SQL returning JSON
- JSON
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 = 1
) as "data"
from dual
declare
l_return clob;
begin
l_return := 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 = 1
) as "data"
from dual
]';
return l_return;
end;
declare
l_cursor sys_refcursor;
l_return clob; -- can also be varchar2, make sure it corresponds to the structure in the JSON
begin
apex_json.initialize_clob_output(dbms_lob.call, true, 2);
open l_cursor for
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 = 1
) as "data"
from dual
apex_json.write(l_cursor);
l_return := apex_json.get_clob_output;
return l_return;
end;
[
{
"filename": "file1",
"data": [
{
"cust_first_name": "John",
"cust_last_name": "Dulles",
"cust_city": "Sterling",
"orders": [
{
"order_total": 2380,
"order_name": "Order 1",
"product": [
{
"product_name": "Business Shirt",
"quantity": 3,
"unit_price": 50,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Trousers",
"quantity": 3,
"unit_price": 80,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Jacket",
"quantity": 3,
"unit_price": 150,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Blouse",
"quantity": 3,
"unit_price": 60,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Skirt",
"quantity": 3,
"unit_price": 80,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Ladies Shoes",
"quantity": 2,
"unit_price": 120,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Belt",
"quantity": 2,
"unit_price": 30,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Bag",
"quantity": 4,
"unit_price": 125,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Mens Shoes",
"quantity": 2,
"unit_price": 110,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Wallet",
"quantity": 2,
"unit_price": 50,
"image": "---base64 encoded image---"
}
]
}
]
}
]
}
]
Providing the appending file using Init PL/SQL.
- Init PL/SQL
- JSON
aop_api_pkg.g_append_files_sql := q'[select file_name as filename, mime_type, file_content as file_blob
from apex_application_static_files
where file_name = 'Sample_Append_File.pdf'
order by filename]';
"append_files": [
{
"name": "Sample_Append_File.pdf",
"mime_type": "application/pdf",
"file_content": "... base64ecoded of the file ...",
"file_source": "base64"
}
],
Template
The template file using different tags for the provided data and the sample append file are as follows
Sample_Append_File.pdf template.docx
Output
Upon processing the template, append file and data, the following output is obtained.
Example for Prepend File
Hereby the example to show the usage of Prepend File using AOP.
Data Source
The data source for the different example are provided below.
- SQL
- PL/SQL returning SQL
- PL/SQL returning JSON
- JSON
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 = 1
) as "data"
from dual
declare
l_return clob;
begin
l_return := 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 = 1
) as "data"
from dual
]';
return l_return;
end;
declare
l_cursor sys_refcursor;
l_return clob; -- can also be varchar2, make sure it corresponds to the structure in the JSON
begin
apex_json.initialize_clob_output(dbms_lob.call, true, 2);
open l_cursor for
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 = 1
) as "data"
from dual
apex_json.write(l_cursor);
l_return := apex_json.get_clob_output;
return l_return;
end;
[
{
"filename": "file1",
"data": [
{
"cust_first_name": "John",
"cust_last_name": "Dulles",
"cust_city": "Sterling",
"orders": [
{
"order_total": 2380,
"order_name": "Order 1",
"product": [
{
"product_name": "Business Shirt",
"quantity": 3,
"unit_price": 50,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Trousers",
"quantity": 3,
"unit_price": 80,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Jacket",
"quantity": 3,
"unit_price": 150,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Blouse",
"quantity": 3,
"unit_price": 60,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Skirt",
"quantity": 3,
"unit_price": 80,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Ladies Shoes",
"quantity": 2,
"unit_price": 120,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Belt",
"quantity": 2,
"unit_price": 30,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Bag",
"quantity": 4,
"unit_price": 125,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Mens Shoes",
"quantity": 2,
"unit_price": 110,
"image": "... base64ecoded of image ..."
},
{
"product_name": "Wallet",
"quantity": 2,
"unit_price": 50,
"image": "---base64 encoded image---"
}
]
}
]
}
]
}
]
Providing the prepending file using Init PL/SQL.
- Init PL/SQL
- JSON
aop_api_pkg.g_prepend_files_sql := q'[select file_name as filename, mime_type, file_content as file_blob
from apex_application_static_files
where file_name = 'Sample_Prepend_File.pdf'
order by filename]';
"prepend_files": [
{
"name": "Sample_Prepend_File.pdf",
"mime_type": "application/pdf",
"file_content": "... base64ecoded of the file ..."
"file_source": "base64"
}
],
Template
The template file using different tags for the provided data and the sample prepend file are as follows
Sample_Prepend_File.pdf template.docx
Output
Upon processing the template, prepend file and data, the following output is obtained.
Example for Append and Prepending Files
Data Source
The data source is same as provided for above example and the example for Init PL/SQL for both append and prepend files are as shown below.
- Init PL/SQL
- JSON
aop_api_pkg.g_prepend_files_sql := q'[select file_name as filename, mime_type as mime_type, file_content as file_blob
from apex_application_static_files
where file_name = 'logo-apex-office.png' order by filename]';
aop_api_pkg.g_append_files_sql := q'[select file_name as filename, mime_type, file_content as file_blob
from apex_application_static_files
where file_name = 'NASAFactSheet.pdf'
order by filename]';
"prepend_files": [
{
"filename": "logo-apex-office.png",
"mime_type": "image/png",
"file_source": "base64",
"file_content": "... base64ecoded of image ..."
}
],
"append_files": [
{
"filename": "NASAFactSheet.pdf",
"mime_type": "application/pdf",
"file_source": "base64",
"file_content": "... base64ecoded of PDF file ..."
}
]
Template
The template file using different tags for the provided data, image file for prepend and pdf for append file are as follows
NASAFactSheet.pdf template.docx logo-apex-office.png
Output
Upon processing the template, prepend file and data, the following output is obtained.
Example for Appending/Prepending EML(Email) Files
Email files might contain attachments, which are appended/prepended to the processed document. The following example shows how to append a EML file with an attachment to the processed document. If you have an EML file to append or prepend, you can provide additional parameters to specify some information on the attachment. Available parameters are:
output_attachment_text
- The text to be displayed in the document for the attachment. It can be a string with provision of additional variables. For example "Current Page: {attachmentCurrentPage} Total Pages: {attachmentTotalPage} Attachment Number : {attachmentIndex} of {attachmentFilename}" The variables are replaced by page number total pages index and filename of attachment. The possible variables are attachmentCurrentPage, attachmentTotalPage, attachmentIndex and attachmentFilename. If not specified, default text used is "{attachmentFilename} | Attachment - {attachmentIndex} | Page {attachmentCurrentPage} of {attachmentTotalPage}". It can be provided in init PL/SQL as g_output_attachment_text.output_attachment_text_position
- The position of the text in the document. It can be one of the following values: "top-left", "center", "top-right", "bottom-left", "bottom-right". If not specified, default value used is "top-left" It can be provided in init PL/SQL as g_output_attachment_text_position.
Data Source
The data source is same as provided for above example and the example for Init PL/SQL for both append and prepend files are as shown below.
- Init PL/SQL
- JSON
aop_api_pkg.g_prepend_files_sql := q'[select file_name as filename,
mime_type as mime_type,
file_content as file_blob
from apex_application_static_files
where file_name = 'eml_attachment_sample.eml' order by filename]';
aop_api_pkg.g_output_attachment_text := 'Filename: {attachmentFilename} | Attachment {attachmentIndex} | Page {attachmentCurrentPage} of {attachmentTotalPage}';
aop_api_pkg.g_output_attachment_text_position := 'top-right';
"output":{
"output_encoding": "raw",
"output_type": "docx",
"output_attachment_text": "Filename: {attachmentFilename} | Attachment {attachmentIndex} | Page {attachmentCurrentPage} of {attachmentTotalPage}",
"output_attachment_text_position": "top-right"
}
"prepend_files": [
{
"filename": "eml_attachment_sample.eml",
"mime_type": "message/rfc822",
"file_source": "base64",
"file_content": "... base64ecoded of EML file..."
}
],
Template
The template file using different tags for the provided data and pdf for append file are as follows
eml_attachment_sample.eml template.docx
Output
Upon processing the template, prepend file and data, the following output is obtained.
Appending / Prepending Password Protected PDF Files
Available From: v24.1AOP allows appending or prepending password protected PDF files. The read password for the password protected PDF can be provided in the Init PL/SQL code using the variable g_append_files_sql
or g_prepend_files_sql
.
Here is an example of how to provide the read password for the password protected PDF file.
aop_api_pkg.g_append_files_sql := q'[select file_name as filename, mime_type, file_content as file_blob, 'your_pdf_read_password' as read_password
from apex_application_static_files
where file_name = 'NASAFactSheet.pdf'
order by filename]';
Converting without Template
Available From: v18.1AOP allows converting files without a template, resulting in the append/prepend files being converted to PDF and stitched together.
Example
Hereby the example to show the conversion of different file types to PDF as a converter. The following image shows the setting of dynamic action where the Template Type and Data Type are set None and Output To is PDF. Also, files are sent through Init PL/SQL Code using the variables, g_prepend_files_sql
and g_append_files_sql
.
Data Source
- Init PL/SQL
- JSON
aop_api_pkg.g_prepend_files_sql := q'[SELECT file_name as filename, mime_type, file_content as file_blob
FROM apex_application_static_files
WHERE file_name IN ('about-aop.docx', 'Astro.jpg');]';
aop_api_pkg.g_append_files_sql := q'[select file_name as filename, mime_type, file_content as file_blob
from apex_application_static_files
where file_name IN ('sample-report.xlsx','sample-html.html');]';
"prepend_files": [
{
"filename": "about-aop.docx",
"mime_type": "application/vnd.openxmlformats-officedocument.wordprocessingml.document",
"file_source": "base64",
"file_content": "... base64 encoded string of file ..."
},
{
"filename": "Astro.jpg",
"mime_type": "image/jpeg",
"file_source": "base64",
"file_content": "... base64 encoded string of file ..."
}
],
"append_files": [
{
"filename": "sample-html.html",
"mime_type": "text/html",
"file_source": "base64",
"file_content": "... base64 encoded string of file ..."
},
{
"filename": "sample-report.xlsx",
"mime_type": "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
"file_source": "base64",
"file_content": "... base64 encoded string of file ..."
}
]
Files
The files used for the conversion are given below.
Here, about-aop.docx and Astro.jpg are provided through Prepend Files and sample-report and sample-html are provided through Prepend Files in the respective order.
Astro.jpg about-aop.docx sample-report.xlsx
sample-html.html contents:
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Black Goose Bistro</title>
</head>
<body style="background-color:#faf2e4;margin: 30px 15%;font-family: sans-serif;">
<h1
style="text-align: center;font-family: serif;font-weight: normal;text-transform: uppercase;border-bottom: 1px solid #57b1dc;">
<img src="https://learningwebdesign.com/4e/materials/chapter04/bistro/blackgoose.png"
alt="Black Goose logo"><br>Black Goose Bistro
</h1>
<h2 style="color: #d1633c;font-size: 1em;">The Restaurant</h2>
<p>The Black Goose Bistro offers casual lunch and dinner fare in a hip atmosphere. The menu changes regularly to
highlight the freshest ingredients.</p>
<h2>Catering</h2>
<p>You have fun... <em>we'll handle the cooking</em>. Black Goose Catering can handle events from snacks for bridge
club to elegant corporate fundraisers.</p>
<h2>Location and Hours</h2>
<p>Seekonk, Massachusetts;<br>
Monday through Thursday 11am to 9pm, <br>Friday and Saturday, 11am to midnight</p>
</body>
</html>
Output
Upon processing the given files, following PDF output is obtained.
Ignoring Conversion Errors in Append/Prepend Files
Available From: v24.2.1AOP allows ignoring conversion errors in append/prepend files. If the conversion of the append/prepend file fails, the error can be ignored and the conversion can continue. The filenames which failed are prepended to the first page of the result. For that you must set the following parameter in the Init PL/SQL code.
- Init PL/SQL
- JSON
aop_api_pkg.g_output_ignore_conversion_errors := true;
"output": {
"output_ignore_conversion_errors": true,
}