Skip to main content

Append/Prepend/Compare Files

Append and Prepend Files​

Introduction​

Available From: v1.0

AOP 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.

Images

When image is provided for appending or prepending, it will appear on its own page, not pasted into the template file.

Output Type PDF Only

When appending or prepending files, the supported output type is limited to PDF format only.

Tags Parsing

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.

g_output_page_format         := 'letter'        --  varchar2(10)  := a4 (default) or letter
g_output_page_orientation    := 'landscape'     --  varchar2(10)  := portrait (default) or 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.

g_output_ignore_conversion_errors := 'true';           -- boolean  := false(default);
Order of Prepend/Append Files

The order in which files are selected for appending or prepending is crucial, as AOP renders the output based on the file order selected.

aop_api_pkg variables for Prepend/Append files

Available aop_api_pkg variables for Prepend/Append files are listed here

Supported mime types for appending/prepending file
FileMimeTypes
PDFapplication/pdf
Texttext/plain
CSVtext/csv
Markdowntext/markdown
HTMLtext/html
Wordapplication/vnd.openxmlformats-officedocument.wordprocessingml.document
Excelapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet
PowerPointapplication/vnd.openxmlformats-officedocument.presentationml.presentation
JPEGimage/jpeg
PNGimage/png
GIFimage/gif
BMPimage/bmp
MS-BMPimage/x-ms-bmp
EMLmessage/rfc822
MSGapplication/vnd.ms-outlook
HEICimage/heic (supported from AOPv24.2.2)
AVIFimage/avif (supported from v24.3)

Supported File Sources for files​

AOP allows various file sources for append and prepend files, as follows:

  1. Base64
  2. URL
  3. File
  4. FTP
  5. SFTP
  6. Plain

Demo for the usage of these file sources are given below.

Base64​

An example showing base64 as the prepend file source:

 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]';

URL​

An example showing url as the prepend file source:

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]';

File​

An example showing file location in aop server as the prepend file source:

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]';

FTP​

An example showing ftp as the prepend file source:

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]';

SFTP​

An example showing sftp as the prepend file source:

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]';

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."
        }
    ]

Common data source (used in examples)​

Data Source​

The following data source is used by the append, prepend, and append+prepend examples below.

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

Example: Append file​

Configure the append file using Init PL/SQL or 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]';

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.

 Output.pdf  

Example: Prepend file​

Example showing usage of prepend files in AOP. The data source is the same as in Common data source (used in examples).

Providing the prepending file using Init PL/SQL.

 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]';

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.

 Output.pdf  

Example: Append and prepend files​

The data source is the same as in Common data source (used in examples). The following configuration sets both prepend and append files.

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.webp' 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]';

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.webp  

Output​

Upon processing the template, prepend file and data, the following output is obtained.

 Output.pdf  

Example: 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.

The data source is the same as in Common data source (used in examples).

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';

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.

 Output.pdf  

Appending/prepending password protected PDF files​

Available From: v24.1

AOP 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.1

AOP 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​

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.webp');]';

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');]';

Files​

The files used for the conversion are given below.

Here, about-aop.docx and astro.webp are provided through Prepend Files, and sample-report and sample-html are provided through Append Files, in the respective order.

 astro.webp    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.

 output.pdf  

Ignoring conversion errors in append/prepend files​

Available From: v24.2.1

AOP 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.

aop_api_pkg.g_output_ignore_conversion_errors := true;

Compare Files​

Comparing Two Files​

Available From: v24.3

AOP allows comparing two files and generating a PDF with the differences highlighted. Data for the two files can be provided in the Init PL/SQL code using the variables g_compare_files_sql. The output type is limited to PDF only for comparing two files.

PDF comparision can take following options which can be provided in the Init PL/SQL code.

  • Color : The highlight color for content only in the first PDF (removed). If not specified, default value used is "red". In plsql, it can be provided as g_output_pdf_cmp_color. In JSON, it can be provided as output_pdf_compare_color.
  • Color Added : The highlight color for content only in the second PDF (added). When set, enables two-color mode to distinguish the direction of change. If not specified, single-color mode is used (all differences highlighted with color). In plsql, it can be provided as g_output_pdf_cmp_color_added. In JSON, it can be provided as output_pdf_compare_color_added.
  • DPI : The DPI of the output PDF. If not specified, default value used is 300. It should be less than or equal to 600. In plsql, it can be provided as g_output_pdf_cmp_dpi. In JSON, it can be provided as output_pdf_compare_dpi.
  • Threshold : The threshold value for the comparison. If not specified, default value used is 10. It can be between 0 and 255, where 0 is the most sensitive and 255 is the least sensitive. In plsql, it can be provided as g_output_pdf_cmp_threshold. In JSON, it can be provided as output_pdf_compare_threshold.

Available colors: PINK, RED, GREEN, BLUE, YELLOW, CYAN, MAGENTA, WHITE, GRAY, ORANGE, DARK_GRAY, LIGHT_GRAY

Color Modes​

Single-color mode (default): Omit color_added. All differing pixels are highlighted with color (default RED).

Two-color mode: Set both color and color_added to distinguish direction of change.

HighlightMeaning
color (e.g. RED)Content exists in first PDF but not in second (removed)
color_added (e.g. GREEN)Content exists in second PDF but not in first (added)
Blended colorBoth PDFs have content at this pixel but it differs (changed)
Comparision as an independent operations

Please note that currently comparing two files is an independent operation and cannot be combined with other operations like append/prepend files. Other operations are skipped if there are files present in g_compare_files_sql. Also other components like g_prepend_files_sql and g_append_files_sql or template are not required for comparing two files.

Example: Single-color comparison (default)​

Hereby the example to show the comparison of two files using AOP with the default single-color mode. All differences are highlighted in a single color.

aop_api_pkg.g_compare_files_sql := q'[
    SELECT file_name AS filename, 
           mime_type, 
           file_content AS file_blob
    FROM apex_application_static_files
    WHERE file_name IN ('base_test.docx', 'comapre_test.pdf')
    ORDER BY filename
]';

-- Optional: customize color, dpi, threshold
-- aop_api_pkg.g_output_pdf_cmp_color     := 'PINK';
-- aop_api_pkg.g_output_pdf_cmp_dpi       := 150;
-- aop_api_pkg.g_output_pdf_cmp_threshold := 20;

Example: Two-color comparison​

This example shows the two-color mode where removed content and added content are highlighted in different colors, making it easy to distinguish the direction of change.

aop_api_pkg.g_compare_files_sql := q'[
    SELECT file_name AS filename, 
           mime_type, 
           file_content AS file_blob
    FROM apex_application_static_files
    WHERE file_name IN ('base_test.docx', 'comapre_test.pdf')
    ORDER BY filename
]';

-- Two-color mode: RED for removed, GREEN for added
aop_api_pkg.g_output_pdf_cmp_color       := 'RED';
aop_api_pkg.g_output_pdf_cmp_color_added := 'GREEN';
aop_api_pkg.g_output_pdf_cmp_dpi         := 300;
aop_api_pkg.g_output_pdf_cmp_threshold   := 10;

Files​

The files used for the conversion are given below.

Here, base_test.docx and compare_test.pdf are provided through Compare Files in the respective order.

 base_test.docx    compare_test.docx  

Output​

Upon processing the given files, following PDF output is obtained.

 Output.pdf