Skip to main content

Request Options

APEX Office Print offers various options for handling output files, allowing you to choose the approach that best suits your needs.

Here are a few of these options, explained below.

Asynchronous Call​

The Asynchronous Call option is utilized when requesting a large file that may require a significant amount of time for processing. AOP returns the URL and using the returned URL, the file can be downloaded later. To use this feature, you need to select "Asynchronous (returns URL)" in the "Output To" field. Additionally, you must set the procedure in the "Procedure Name" field to handle the response provided by AOP after the file processing request has been initiated.

Once the asynchronous call request is initiated, the procedure should handle the response from AOP using the parameters g_async_status, g_async_message, and g_async_url. The returned URL, accessible through g_async_url, can be used to download the processed file. This URL contains a Unique Reference ID specific to the file.

When a GET request is made to the obtained URL (g_async_url), it can either return the output file directly or respond with a status and message indicating the processing status.

For a practical reference on how to implement the asynchronous call, you can explore the AOP Sample Application, specifically the Examples section, and navigate to the Asynchronous Call.

Important Note

From AOPv24.1, You can get status of the async request(Processing, Successful, Not Found and Failed) using respone status code of the response.

Encrypted file on the server is deleted after 10 hours so, a valid url with valid secret key may result in an error message.

  1. Successful Response (200): The request is successful and will return a file.
  2. Request Not Completed (202): The request is still processing, and you will need to check back later. It returns a JSON with additional information.
  3. Request Not Found (404): The file you are looking for cannot be found. This could be because the file might have been deleted. Note: A file will be deleted if any of the following conditions are met:
    1. Your request is older than 10 hours.
    2. You have set the 'delete_after_download' parameter to true while downloading.
  4. Request Failed (500): An error occurred on the server while processing your request. It returns a JSON with more information about the error.

Output Location (Directory on AOP Server)​

For on-prem users, AOP provides the option to store processed output files in the server or disk running the AOP. To enable this feature, you need to set "enable_save": true in the aop_config.jsonc configuration or run the AOP with the --enable-save argument.

For more information and available options of enable_save, go to Save file to local disk or server.

Additionally, you will need to provide additional PL/SQL code for the specific request for which you want the output to be stored on the server. This can be accomplished by providing the g_output_directory in the Init PL/SQL Code section.

aop_api_pkg.g_output_directory := './'

Resources Location​

It is possible to provide local path, URL, FTP and SFTP URL for the template, images, subtemplates, append, and prepend files.

Enable Local Resources

To ensure that fetching from local path in AOP works properly, it must have access to local resources. You can enable local resources by passing the parameter --enable_local_resources when running the AOP executable, or by setting the "enable_local_resources": true in the aop_config.jsonc file.

Relative and Absolute Path

AOP allows access to files using both absolute and relative paths, with the relative path being relative to the location of the AOP executable.

Template Source (with relative to AOP Server)​

For AOP on-prem users, it is possible to specify the local directory of the template where the AOP Server is running. To utilize this feature, select Filename (with path relative to AOP server) as the Template Type and provide the template location in the Template Source field, following the example below.

Using local path for images, subtemplate, append and prepend files.​

Similarly, it is possible to use local path for images, subtemplate, append and prepend files if local resources is enabled in AOP.

Local path for image​

Hearby the example to show the usage of local directory for the image. 

select 'file1' as "filename",
       cursor (
         select './images/img28-500x375.jpg'         as "image1",            --relative path       
                'D:/TempFiles/images/29-500x500.jpg' as "image2"             --absolute path
         from dual
       )       as "data"
from dual
Extension

Including the file extension is mandatory and if AOP fails to access image due to permisson error or unavailability of file, an error might be thrown or sample invalid image might get replaced by AOP.

Local path for subtemplate, append and prepend files.​

Hereby the example to show the usage of local directory for the subtemplate, append and prepend files.

aop_api_pkg.g_append_files_sql          :=q'[
    select 'file_app' as  filename,
    'application/vnd.openxmlformats-officedocument.wordprocessingml.document' as mime_type,
    'D:/TestFiles/file_app.docx' as file_on_aop_server from dual
]';

aop_api_pkg.g_prepend_files_sql          :=q'[
    select 'file_pre' as  filename,
    'application/vnd.openxmlformats-officedocument.wordprocessingml.document' as mime_type,
    'D:/TestFiles/file_pre.docx' as file_on_aop_server from dual
]';

aop_api_pkg.g_sub_templates_sql:=q'[
    select 'subtemplate1' as  filename,
    'application/vnd.openxmlformats-officedocument.wordprocessingml.document' as mime_type,
    'D:/TestFiles/subtemplate1.docx' as file_on_aop_server from dual
]';

Using FTP and SFTP for images subtemplate, append and prepend files​

Users can also utilize the FTP and SFTP links to retrieve the files used in the request. The example of configurations that uses FTP and SFTP URLS for template, subtemplate, image, prepend and append files is as shown below.

URL Format

Supported URL format for ftp and sftp: "(s)ftp://username:password@hostname:port/filelocation.ext".
Examples:
ftp://admin_aop:sdfbk389sdf@192.168.0.25:21/file1.png
sftp://admin_aop:askpe45asbf@192.168.0.25:22/images/file1.png

Here is the SQL code that demonstrates the usage of an FTP URL for the image, along with retrieving the customer name and city from the table.

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",
                'ftp://foo:pass@127.0.0.1:21/test/images/hello.png' as "image1"
         from aop_sample_customers c
         where c.customer_id = 1
       )       as "data"
from dual;

Hereby, the initial PL/SQL code that shows the usage of FTP and SFTP URL to retrieve prepend, append and subtemplate files.

aop_api_pkg.g_sub_templates_sql := q'[
    select 'subtemplate1'                                                        as  filename,
    'application/vnd.openxmlformats-officedocument.wordprocessingml.document'    as mime_type,
    'sftp://foo:pass@127.0.0.1:22/test/subtemplates/subtemplate1.docx'           as url_call_from_aop
     from dual
]';

aop_api_pkg.g_append_files_sql := q'[
    select 'file_app'                                                            as filename,
       'application/vnd.openxmlformats-officedocument.wordprocessingml.document' as mime_type,
       'ftp://foo:pass@127.0.0.1:21/test/append_files/file_app.docx'             as url_call_from_aop
    from dual
]';

aop_api_pkg.g_prepend_files_sql := q'[
    select 'file_pre'                                                            as filename,
       'application/vnd.openxmlformats-officedocument.wordprocessingml.document' as mime_type,
       'sftp://foo:pass@127.0.0.1:22/test/prepend_files/file_pre.docx'           as url_call_from_aop
    from dual
]';

For Template, the selected template type is URL (call from AOP) and has following template source.

ftp://foo:pass@127.0.0.1:21/test/template/template.docx

IPP Printing​

Available From: v3.1

AOP has an ability to print directly to an IP printer, as long as it's available from the server AOP is running on. To send the output file to IP printer from AOP Server, additional init PL/SQL code needs to be provided as shown below.

aop_api_pkg.g_ip_printer_location         := 'http://10.0.14.223:631/';           -- required
aop_api_pkg.g_ip_printer_version          := '1.1';                               -- required  
aop_api_pkg.g_ip_printer_requester        := nvl(apex_application.g_user, USER);  -- optional
aop_api_pkg.g_ip_printer_job_name         := 'AOP';                               -- optional  
aop_api_pkg.g_ip_printer_return_output    := 'true';                              -- optional (null or 'Yes' or 'true')

Testing IP Printer availability from AOP Server.​

To verify the accessibility of an IP Printer from the AOP Server, there are several methods available for testing, including the following options.

  1. Using SQL Commands.
  2. Using Browser
  3. IPP Check Executable.
  4. AOP Webeditor

Using SQL Commands​

One way to test the accessibility of an IP Printer from the AOP Server is by sending a GET request using APEX SQL Commands. This method enables you to confirm the connection and ensure that the IP Printer can be reached from the AOP Server.

Specify the ipp_url and version as http(s)://aop_server_url:port?ipp_url=ipp_printer_url&version=version as shown below

SQL Command to check the IP Printer reachability

select apex_web_service.make_rest_request('http://host.docker.internal:8012/ipp_check?ipp_url=http://localhost:3000&version=1.1','GET') from dual;

This returns the status of the ipp printer based on given ipp_url and version.

Using Browser​

Another way of IPP Check is by using web browser where you enter the URL as shown below. The URL format is :

http(s)://aop_server_url:port?ipp_url=ipp_printer_url&version=version

Example URL:

http://192.168.1.68:8012/ipp_check?ipp_url=http://localhost:3000&version=1.1

IPP Check Executable​

AOP also provides an executable to test the IP Printer reachability. You can download the executable(available for windows and linux) from Downloads section of AOP portal.
You should provide IPP URL while executing the IPP check executable, following the provided format below.

AOP Webeditor​

AOP webeditor has an interface for the IPP Check as shown below.

When you click the IPP Check button, a dialog box will be displayed where you can enter the IP Printer URL and version. After entering the information and clicking PROCESS, if the IP Printer URL is valid and reachable, further details about the IP Printer will be shown. However, if the IP Printer is not reachable, an error message will be displayed, indicating that it is not accessible.

Error while ipp_check

If the IPP check returns an error, it indicates that the IP printer is unreachable from the AOP server. In such cases, there is a possibility that the output may not be successfully printed on the IP printer. It is important to resolve any connectivity issues or address the error in order to establish a functional connection between the AOP server and the IP printer.

Logging​

If AOP is initiated with the --enable_printlog flag or if the "enable_printlog" setting is set to true in the aop_config.jsonc file, it will generate a log file named server_printjob.log and starts writing/appending log for each request sent to AOP. You can add additional logging information next to the one AOP is logging by default, by adding additional keys and values in the logging object.

You need to pass additional logging information in the g_logging as a string which consist additional keys and values. An example to show how the additional logging can be sent to AOP Server is as shown below.

aop_api_pkg.g_logging:= '"request_id":"123", "request_app":"APEX", "request_user":"RND"';

Template​

Template Delimiters​

Using the global variables, It is possible to specify the tag delimiters used in the template. The default opening and closing tag delimiters are { and } (opening and closing curly brackets). The available options for opening and closing tag delimiters are {, {{, <, << and }, }}, >, >> respectively.

The example Init PL/SQL to set {{ and }} for the delimiters is as shown below.

aop_api_pkg.g_template_start_delimiter := '{{';
aop_api_pkg.g_template_end_delimiter := '}}';

The delimiters specified must be used in template. If you specify delimiters as mentioned in above example your template should contain tags wrapped in {{ }}, for example {{#orders}} ... {{/orders}}.

HTML Template Content​

Available From: v3.4

AOP allows the usage of HTML content as a template. This is equivalent to using the {_html} tag in Word to create the template which is then again passed to the AOP with the data for rendering.

Example​

The example below shows you how to apply Word templating features using HTML as source.

Data Source​

Hereby, the data source for the given template.

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"
                           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
Template​

The template type selected is PL/SQL Function (returning JSON) and the template source is

declare 
  l_return        clob; 
  l_template      clob; 
  l_template_type aop_template.template_type%type; 
begin 
  l_template_type := 'html';
  l_template := replace(replace(:P173_HTML_TEMPLATE,chr(10),''),chr(9),'');

  l_return := '{ "html_template_content":"'||replace(l_template,'"', '\"')||'",'||
              '  "template_type": "' || l_template_type || '",' ||
              '  "orientation":"landscape"}'; 

  return l_return;
end;

The HTML is returned from the region :P173_HTML_TEMPLATE which consist the following static value.

:P173_HTML_TEMPLATE
<p><strong> Simple demo to show the usage of HTML Content as a Template</strong></p>
<br/>
<p style="text-align:center;font-weight:bold;">Order Receipt</p>
<div>
    <p>Customer Name : {cust_first_name}&nbsp;{cust_last_name}</p>
    <p>Customer Address : {cust_city}</p>
    <p>{#orders}</p>
    <p style="text-align:center;font-weight:bold;">{order_name}</p>
    <table border="1" cellpadding="1" cellspacing="1" style="width:500px">
        <tbody>
            <tr>
                <td>Product Name</td>
                <td>Quality</td>
                <td>Unit Price</td>
                <td>Total Cost</td>
            </tr>
            <tr>
                <td>{#product}{product_name}</td>
                <td>{quantity}</td>
                <td>{unit_price}</td>
                <td>{quantity*unit_price}{/product}</td>
            </tr>
        </tbody>
    </table>
    {/orders}
</div>
Output​

The selected output type is docx, and upon processing the following result is obtained.

 html_to_word_output.docx  

Template Caching​

Available From: v3.4

In case you anticipate many prints with the same template, you can let AOP know to cache the template. This means that the requests to the AOP Server will be smaller as the template doesn't need to be included. Instead, the hash of the template will be passed.

At first, a request should be made to the server to cache the template including the aop_api_pkg.g_cache_template := true;. This will return the hash of the template in the global variable aop_api_pkg.g_template_cache_hash. It's best to store this hash in a table so on second requests you can pass the hash. You can also use the procedure to store the hash, the procedure has an extra parameter p_template_cache_hash. For the better understanding, please refer to AOP Sample Application > Batch > Cache the template.

aop_api_pkg.g_cache_template := true;
Caching Supported Formats

Currently only Word, Excel, Markdown, Text, HTML, CSV and PowerPoint hash-template generation is supported.

When you want to use the hash, you pass the hash with the same global variable aop_api_pkg.g_template_cache_hash. AOP will include the hash and not the template. By default when the hash is not valid anymore, AOP will give an error. You can also tell AOP to send the template with it in case the hash is not valid anymore by setting the global variable aop_api_pkg.g_use_template_when_no_cache to Y(es).

In the PL/SQL Init Code specify:

aop_api_pkg.g_template_cache_hash := v('P82_TEMPLATE_CACHE_HASH');
aop_api_pkg.g_use_template_when_no_cache := aop_api_pkg.c_y;

Also specify a procedure with the following definition:

create procedure store_template_hash_prc(
    p_output_blob         in blob,
    p_output_filename     in varchar2,
    p_output_mime_type    in varchar2,
    p_template_cache_hash in varchar2)
is
begin
  -- update the table to store the hash of the template
  update aop_template
     set template_cache_hash = p_template_cache_hash
   where id = v('P1_ID');  
     
  commit;    

  -- set the hash in session state, for other requests
  apex_util.set_session_state('P1_TEMPLATE_HASH', p_template_cache_hash);
end store_template_hash_prc;

You can manually check if an hash is still valid by using the function aop_api_pkg.is_valid_template_hash.

Check if an hash is still valid
if aop_api_pkg.is_valid_template_hash(p_aop_url => :AOP_URL, p_hash => :P82_TEMPLATE_HASH)
then 
  return 'VALID';
else
  return 'INVALID';
end if;

You can also use SQL Command or URL to verify template hash. Refer to Rest API section for the example. You can also find the way to renew and invalidate the hash in the API section.

Similarly, Webeditor also provides the interface to renew, validate and invalidate template hash in Utility Section. Refer to Webeditor Section

Files Array​

AOP allows export of multiple files and download them all a zip files.

Example​

Data Source​

select
  'file_' || to_char(d.customer_id) 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 c.customer_id = d.customer_id
  ) as "data"
from aop_sample_customers d
where d.customer_id in (1,2,3)

In the PL/SQL Init Code specify 'aop_api_pkg.g_output_filename := '<your_filename>.zip';'.

Template​

The template is a simple order confirmation letter with a list order.

 sample-template.docx  

Output​

On processing the above template and data, The Zip file is provided as a output that consist multiple files.

 output.zip  

Cloud Based Authentication​

The current version of AOP supports up to six cloud based services. The ones currently supported are:

For cloud based authentication, select Output To to Cloud(Dropbox, Google Drive, OneDrive, Amazon S3).

Amazon S3​

Access Token FormatDescription
JSON ObjectAmazon S3 authentication requires three keys in order to save a file on its cloud: "AWS Region name", "AWS access key ID" and "AWS secret access key". More information about this can be found here. AWS keys

In order to send file to the aws s3 bucket from AOP server, additional init PL/SQL code should be provided as shown below.

aop_api_pkg.g_cloud_provider := 'aws_s3';
aop_api_pkg.g_cloud_location := v('P5000_DIRECTORY');
aop_api_pkg.g_cloud_access_token := 'replace with your token';

AWS Security Policy​

The majority of policies are saved as JSON documents in AWS. JSON policy documents, such as identity-based policies and permissions boundaries, are attached to users or roles. Resource-based policies are attached to specific resources. AWS Organizations organizational units (OUs) can have JSON policy documents known as SCPs, which have a restricted syntax. ACLs are also attached to resources but require a different syntax. When assuming a role or a federated user session, session policies in the form of JSON policies are provided. More more details visit AWS access policies.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetBucketLocation",
                "s3:ListAllMyBuckets"
            ],
            "Resource": "arn:aws:s3:::*"
        },
        {
            "Effect": "Allow",
            "Action": "s3:*",
            "Resource": [
                "arn:aws:s3:::mybucket",
                "arn:aws:s3:::mybucket/*"
            ]
        }
    ]
}

Dropbox​

AOP allows user to add output files directly to the Dropbox storage. To do this, user need to get the token from the https://dropbox.github.io/dropbox-api-v2-explorer/#auth_token/from_oauth1 and pass the token to the AOP in the Init PL/SQL as shown in example below. 

aop_api_pkg.g_cloud_provider := 'dropbox';
aop_api_pkg.g_cloud_location := 'aop-output-test/';
aop_api_pkg.g_cloud_access_token := q'[{"access_key": "----access-key----"}]';

Google Drive​

To upload the output file to Google Drive, AOP utilizes an OAuth 2.0 bearer access token. Therefore, users are required to set up their project and application at Google Cloud and obtain the access token for using this service. For detailed instructions on project initiation and application setup, please refer to the following link: https://developers.google.com/identity/protocols/oauth2.

Scope

The application should be granted the scope "https://www.googleapis.com/auth/drive" for proper functionality.

In order to send file to the google drive from AOP server, additional init PL/SQL code should be provided as shown below.

aop_api_pkg.g_cloud_provider := 'gdrive';
aop_api_pkg.g_cloud_location := 'aop-output-test/';
aop_api_pkg.g_cloud_access_token := q'[{ "access_key": "----access-key----"}]';

Microsoft OneDrive​

In order to utilize Microsoft OneDrive as the output location, users must set up App registration and Authentication to access the OneDrive API and obtain an access token. This access token is utilized by AOP to directly upload or save the processed output file to OneDrive. For comprehensive instructions on the setup and authentication process to acquire the access token, please consult the following resource: https://learn.microsoft.com/en-us/onedrive/developer/rest-api/getting-started.

In order to send file to the onedrive from AOP server, additional init PL/SQL code should be provided as shown below.

aop_api_pkg.g_cloud_provider := 'onedrive';
aop_api_pkg.g_cloud_location := 'aop-output-test/';
aop_api_pkg.g_cloud_access_token := q'[{ "access_key": "----access-key----"}]';

FTP (File Transfer Protocol)​

AOP offers the capability to save the processed output file directly to an FTP server. To successfully perform this operation, users are required to provide the credentials such as the host URL, port, username, and password of the FTP server.

In order to send the output file to the SFTP Server from AOP server, additional init PL/SQL code should be provided as shown below.

aop_api_pkg.g_cloud_provider := 'ftp';
aop_api_pkg.g_cloud_location := '/';
aop_api_pkg.g_cloud_access_token := q'[{"host": "192.168.1.68","port":"21","user":"foo","password":"pass"}]';

SFTP (Secure File Transfer Protocol)​

AOP also offers the capability to save the processed output file directly to an SFTP server. To successfully perform this operation, users are required to provide the credentials such as the host URL, port, username, and password of the SFTP server.

In order to send the output file to the SFTP Server from AOP server, additional init PL/SQL code should be provided as shown below.

aop_api_pkg.g_cloud_provider := 'sftp';
aop_api_pkg.g_cloud_location := '/';
aop_api_pkg.g_cloud_access_token := q'[{"host": "192.168.1.68","port":"22","user":"foo","password":"pass"}]';