Skip to main content

Append/Prepend/Compare 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
Markdowndown text/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 files sources for the append and prepend files which are 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."
        }
    ]

Example for Append File​

Data Source​

Hereby examples of data source for different options.

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

Providing the appending file using Init PL/SQL.

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

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

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

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

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

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.

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​

Hereby the example to show the comparison of two files using AOP.

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

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