Skip to main content

AOP Process

Overview

If you are using APEX 5.1, the Dynamic Action plug-in will always work, whereas the Process plug-in only works if the “Reload on Submit” attribute (of the page) is set to “Always” (note this attribute is new in 5.1). This is due to a change how APEX 5.1 is handling Page Processing. If you would import an APEX 5.0 app in 5.1 by default it’s set to Always reload on submit, but if you create a new app in 5.1 it’s set to “Only for Success” which causes the process type plug-in to fail.

Settings

Template Type

AOP Template

AOP will generate a Word document with a starting template based on the data (JSON) that is submitted.
Documentation is also added on the next page(s) that describe the functions AOP will understand.

AOP Report

AOP will generate a report for you based on the Print Attributes specified in the given region (in case multiple regions are specified, the first region is used for the Print Attributes).

APEX Report

From APEX 20.2 onwards, APEX supports the creation of native Excel and PDF of a region. Using APEX Report will use this feature. Note: only 1 region (static id) can be rendered. An alternative to APEX Report is an AOP Report, which uses the AOP rendering and which supports also HTML expressions.

APEX Report (Data only)

From APEX 20.2 onwards, APEX supports the creation of native Excel and PDF of a region. Using APEX Report will use this feature. Data only will remove highlights etc. and just export the data. Note: only 1 region (static id) can be rendered. An alternative to APEX Report is an AOP Report, which uses the AOP rendering and which supports also HTML expressions.

Static Application Files

Enter the filename of the file uploaded to your Shared Components > Static Application Files
e.g. aop_template_d01.docx

Static Workspace Files

Enter the filename of the file uploaded to your Shared Components > Static Workspace Files
e.g. aop_template_d01.docx

SQL
Query that returns two columns: template_type and file (in this order)

  • template_type: docx, xlsx, pptx, html, md
  • file: blob column

PL/SQL Function (returning SQL)

Enter a PL/SQL procedure that returns a select statement with two columns: template_type and file (in this order)

  • template_type: docx, xlsx, pptx, html, md
  • file: blob column
note

Note that you can use bind variables e.g. :PXX_ITEM.

PL/SQL Function (returning JSON)

Return JSON object with following format:

{
"file":"clob base 64 data",
"template_type":"docx,xlsx,pptx"
}

JSON

Add the JSON of "template" in here. Ex:

{"filename":"aop_template_d01.docx", "template_type":"docx"}

Filename (with path relative to AOP server)

Enter the path and filename of the template which is stored on the same server AOP is running at.

Filename (with database directory)

Enter the Database directory colon separated with the filename.
e.g. AOP_TEMPLATE:my_template.docx

Example how to create Database directory:
CREATE DIRECTORY AOP_TEMPLATE AS '/home/oracle/aop_template';

URL (call from DB)

Enter the url to your template in docx, xlsx or pptx. e.g. https://www.apexofficeprint.com/templates/aop_template_d01.docx Always make sure your url ends with the filename. E.g. for Google Drive add to the end of the url &aop=.docx
This call is done from the database, so the database server needs to have access to the url.

URL (call from AOP)

Enter the url to your template in docx, xlsx or pptx.
e.g. https://www.apexofficeprint.com/templates/aop_template_d01.docx
Always make sure your url ends with the filename. E.g. for Google Drive add to the end of the url &aop=.docx . This call is done from AOP, so the AOP server needs to have access to the url.

None

To only append or prepend files or to fill PDFs, set the template type to none.

Template Source

The templates need to be of format: Word (docx), Excel (xlsx), PowerPoint (pptx), HTML (html), Text (txt), CSV (csv) or Markdown (md).

Examples

Reference a file in Shared Components > Static Application Files or Static Workspace Files

aop_template.docx

In case you want to specify the template dynamically, use the SQL option with following:

select case 
when mime_type = 'application/octet-stream'
then substr(file_name, instr(file_name,'.',-1)+1)
else mime_type
end as template_type,
file_content as template_blob
from apex_application_static_files
where file_name = :PXX_YOUR_ITEM
and application_id = :APP_ID

Reference a file on the server. Include the path relative to the AOP executable.

Reference a url, accessible from the database.

Reference a url, accessible from the AOP Server.

Specify the region static id where the Print Attributes are specified for the AOP Report.

Reference a database directory and file on the database server. Format: DIRECTORY:FILENAME e.g. AOP_TEMPLATE:my_template.docx

Data Type

SQL

Enter a select statement in which you can use a cursor to do nested records. Use "" as alias for column names to force lower case column names.

You can also use SQL that generates JSON.

Note that you can use bind variables e.g. :PXX_ITEM.

Details of a customer e.g. for a letter

Cursor syntax:

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"
from demo_customers c
where c.customer_id = :PXX_ITEM
) as "data"
from dual

Native JSON database functionality:

select 
json_arrayagg(
json_object(
'filename' value 'file1',
'data' value (select
json_arrayagg(
json_object(
'cust_first_name' value c.cust_first_name,
'cust_last_name' value c.cust_last_name,
'cust_city' value c.cust_city
)
)
from demo_customers c
where c.customer_id = :PXX_ITEM
)
)
) as aop_json
from dual

PL/SQL Function (returning SQL)

Enter a PL/SQL procedure that returns as select statement in which you can use a cursor to do nested records. Use "" as alias for column names to force lower case column names.

Note that you can use bind variables e.g. :PXX_ITEM.

PL/SQL Function (returning JSON)

Return JSON as defined in the URL example above. (see example in help of Data Source)

URL (returning JSON)

The Source should point to a URL that returns a JSON object with following format: { "filename": "file1", "data":[{...}] } If the URL is using an APEX/ORDS REST call it will automatically be wrapped with additional JSON: {"items":[...]} This is ok as the plugin removes it for you.

Classic and/or Interactive Report/ Grid(s)

Enter a comma separated list of static id of one of following region types:

  • Classic Report
  • Interactive Report
  • Interactive Grid

e.g. my_classic_report,ir1

In your template you can include the entire Interactive Report by using the tag {&interactive_1} for the first interactive report, {&interactive_2} for the second etc.

If you just want to get the data and do the styling yourself, you can use for classic report: {#<staticid>}{column}{/<static_id>} or for interactive report {#aopireportdata_1}{column}{/aopireportdata_1}. For interactive grid use {#aopigridoptions<staticid>} {column} {/aopigridoptions<static_id>}

XML (data part)

Data in XML format. No need to specify files or filename.

JSON (data part)

Data in JSON format. No need to specify files or filename.

JSON (REST, GraphQL, Raw)

Enter JSON in format:

[ 
{
"filename": "xxx.docx",
"datasource": "graphql",
"query": "{human(id:\"1000\"){name height}",
"endpoint": "https://api.graph.cool/simple/v1/",
"headers": [{"Content-Type":"application/json"},{"Custom-Auth-Token":"xyz"}]
}
]

The files part of the AOP structure will be filled in with the value specified here.

None

To only append or prepend files or to work with PDFs, set the data type to none.

Data Source

SQL

A SQL statement is the easiest to use, either by using the cursor syntax or native JSON database functionality.

Images need to be base64 encoded. You can reference items by using :ITEM

Examples
Cursor syntax

List of all customers e.g. to send letter to all

select
'file1' as "filename",
cursor
(select
cursor(select
c.cust_first_name as "cust_first_name",
c.cust_last_name as "cust_last_name",
c.cust_city as "cust_city"
from demo_customers c) as "customers"
from dual) as "data"
from dual

Details of all orders of a customer e.g. for invoices

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 demo_order_items i, demo_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from demo_orders o
where c.customer_id = o.customer_id
) "orders"
from demo_customers c
where customer_id = :P1_CUSTOMER_ID
) as "data"
from dual
Native JSON database functionality

Details of a customer e.g. for a letter

select 
json_arrayagg(
json_object(
'filename' value 'file1',
'data' value (select
json_arrayagg(
json_object(
'cust_first_name' value c.cust_first_name,
'cust_last_name' value c.cust_last_name,
'cust_city' value c.cust_city
)
)
from demo_customers c
where c.customer_id = 1
)
)
) as aop_json
from dual

Details of all orders of a customer e.g. for invoices

select 
json_arrayagg(
json_object(
'filename' value 'file1',
'data' value (
select
json_arrayagg(
json_object(
'cust_first_name' value c.cust_first_name,
'cust_last_name' value c.cust_last_name,
'cust_city' value c.cust_city,
'orders' value (
select
json_arrayagg(
json_object(
'order_total' value o.order_total,
'order_name' value 'Order ' || rownum,
'order_lines' value (
select
json_arrayagg(
json_object(
'product_name' value p.product_name,
'quantity' value i.quantity,
'unit_price' value i.unit_price
)
returning clob)
from demo_order_items i, demo_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
)
)
returning clob)
from demo_orders o
where o.customer_id = c.customer_id
)
)
returning clob)
from demo_customers c
where c.customer_id = :P1_CUSTOMER_ID
)
)
returning clob) as aop_json
from dual

PL/SQL Function (returning SQL)

By using PL/SQL to create your own SQL or JSON, you're more flexible. You can use bind variables and page items.

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
demo_order_items i, demo_product_info p
where
o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from
demo_orders o
where
c.customer_id = o.customer_id
) "orders"
from
demo_customers c
where
customer_id = :P4_CUSTOMER_ID
) as "data"
from dual
]';
return l_return;
end;

PL/SQL Function (returning JSON)

By using PL/SQL to create your own SQL or JSON, you're more flexible. You can use bind variables and page items.

declare
l_cursor sys_refcursor;
l_return clob;
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
demo_order_items i,
demo_product_info p
where
o.order_id = i.order_id
and i.product_id = p.product_id
) "product"
from
demo_orders o
where
c.customer_id = o.customer_id
) "orders"
from
demo_customers c
where
customer_id = :P4_CUSTOMER_ID
) as "data"
from dual;
apex_json.write(l_cursor) ;
l_return := apex_json.get_clob_output;
return l_return;
end

URL (returning JSON)

Specify the XML or JSON.

For type URL:

Create (in for example ORDS) a new REST web service with a GET, source type "Query" and format "JSON".

Put the url in this text box, e.g. https://www.apexofficeprint.com/ords/aop/get_data/1

Here's an example of a query which contains a parameter too:

select 
'file1' as filename,
cursor(
select
c.cust_first_name,
c.cust_last_name,
c.cust_city,
cursor(select o.order_total, 'Order ' || rownum as order_name,
cursor(select p.product_name, i.quantity, i.unit_price, APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as image
from demo_order_items i, demo_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) product
from demo_orders o
where c.customer_id = o.customer_id
) orders
from demo_customers c
where customer_id = :id
) as data
from dual

XML (data part)

Specify the XML or JSON.

For type URL:

Create (in for example ORDS) a new REST web service with a GET, source type "Query" and format "JSON".

Put the url in this text box, e.g. https://www.apexofficeprint.com/ords/aop/get_data/1

Here's an example of a query which contains a parameter too:

select 
'file1' as filename,
cursor(
select
c.cust_first_name,
c.cust_last_name,
c.cust_city,
cursor(select o.order_total, 'Order ' || rownum as order_name,
cursor(select p.product_name, i.quantity, i.unit_price, APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as image
from demo_order_items i, demo_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) product
from demo_orders o
where c.customer_id = o.customer_id
) orders
from demo_customers c
where customer_id = :id
) as data
from dual

JSON (data part)

Specify the XML or JSON.

For type URL:

Create (in for example ORDS) a new REST web service with a GET, source type "Query" and format "JSON".

Put the url in this text box, e.g. https://www.apexofficeprint.com/ords/aop/get_data/1

Here's an example of a query which contains a parameter too:

select 
'file1' as filename,
cursor(
select
c.cust_first_name,
c.cust_last_name,
c.cust_city,
cursor(select o.order_total, 'Order ' || rownum as order_name,
cursor(select p.product_name, i.quantity, i.unit_price, APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as image
from demo_order_items i, demo_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) product
from demo_orders o
where c.customer_id = o.customer_id
) orders
from demo_customers c
where customer_id = :id
) as data
from dual

JSON (REST, GraphQL, Raw)

Specify the XML or JSON.

For type URL:

Create (in for example ORDS) a new REST web service with a GET, source type "Query" and format "JSON".

Put the url in this text box, e.g. https://www.apexofficeprint.com/ords/aop/get_data/1

Here's an example of a query which contains a parameter too:

select 
'file1' as filename,
cursor(
select
c.cust_first_name,
c.cust_last_name,
c.cust_city,
cursor(select o.order_total, 'Order ' || rownum as order_name,
cursor(select p.product_name, i.quantity, i.unit_price, APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as image
from demo_order_items i, demo_product_info p
where o.order_id = i.order_id
and i.product_id = p.product_id
) product
from demo_orders o
where c.customer_id = o.customer_id
) orders
from demo_customers c
where customer_id = :id
) as data
from dual

Region Static Id(s)

Define one or more Static Id(s) of the report region. Static ids should be separated by a comma. e.g. ir1,ir2

You can set the Static ID of the region in the region settings (Advanced section).

Special

Specific features of APEX Office Print

Available options include:

Treat all numbers as strings

There's a limitation in APEX with the cursor() statement in SQL that it doesn't remember which datatype the column is in. So when doing to_char(0.9,'990D00') it will return 0.9 as number instead of as string '0.90'. To resolve this, enable this checkbox and concatenate your number with '!FMT!' e.g. '!FMT!'||to_char(35, '990D00') - !FMT! stands for format.

Alternatively if you format your number with the currency sign to_char(35,'FML990D00') Oracle will recognise it as a string and you don't need to use this checkbox.

Report as Labels

Check this box in case you want to use the Classic or Interactive Report data source but print them as Labels (using the Mailings feature in Word).

IR/IG: Show Filters on top

When there're filters applied to the Interactive Report, this checkbox will print them above the report.

IR/IG: Show Highlights on top

When there're highlights applied to the Interactive Report, this checkbox will print them above the report.

IR/IG: Show header with filter (Excel)

When exporting the Interactive Report to Excel, show the header with filter option.

IR/IG: Use Saved Report instead of Report in Session

When defining the Interactive Report source ir1|my_saved_report, the "my_saved_report" will be used, even when the person is looking at a different report in his session session.

IR/IG: Repeat header on every page

When the table spans multiple pages, the header row will be repeated on every page.

Output Filename

The filename can be a hard coded string or reference an APEX item. It does not need to include the file extension. If a file extension is defined that is different the the output type selected, a new file extension will be appended to the filename

Examples

Static: my_file

APEX Item: &P1_FILENAME.

Output Type

Available options include:

  • Word (docx)
  • Excel (xlsx)
  • PowerPoint (pptx)
  • PDF (pdf)
  • HTML (html)
  • Markdown (md)
  • Text (txt)
  • Rich Text Format (rtf)
  • CSV (csv) - Comma separated values file. Text file containing information separated by commas.
  • One Page PDF (pdf)
  • PDF Form Fields (pdf) - Get the form fields defined on a PDF.
  • Word with macros (docm)
  • Excel with macros (xlsm)
  • PowerPoint with macros (pptm)
  • Calendar (ics)
  • Calendar (ifb)
  • OpenDocument Text (odt) - OpenOffice Word
  • OpenDocument Spreadsheet (ods) - OpenOffice Excel
  • OpenDocument Presentation (odp) - OpenOffice PowerPoint
  • XML (xml) - Extensible Markup Language is a markup language that defines a set of rules for encoding documents in a format that is both human-readable and machine-readable.
  • JSON (json) - JSON stands for JavaScript Object Notation. JSON is a lightweight data-interchange format.
  • Defined by APEX Item

Output To

By default the file that's generated by AOP, will be downloaded by the Browser and saved on your harddrive.

Available options include:

Browser (file)

Procedure

This option will call a procedure in a specific format. This option is useful in case you don't need the file on your own harddrive, but for example you want to mail the document automatically. In that case you can create a procedure that adds the generated document as an attachment to your apex_mail.send.

Procedure and Browser (file)

This option allows you to call a procedure first and next download the file to your harddrive. An example is when you first want to store the generated document in a table before letting the browser to download it.

Inline Region (pdf/html/md/txt only)

add data-aop-inline-pdf="Name of Dynamic Action" or data-aop-inline-txt="Name of Dynamic Action" to a region, div, textarea of other.

Procedure and Inline Region (pdf/html/md/txt only)

This option allows you to call a procedure first and next show the output in a region. Add data-aop-inline-pdf="Name of Dynamic Action" or data-aop-inline-txt="Name of Dynamic Action" to a region, div, textarea of other.

Directory (on AOP Server)

Save the file to a directory specified with g_output_directory. The default directory on the AOP Server is outputfiles.

Directory (on Database Server)

Save the file to a database directory specified with g_output_directory.

Example how to create Database directory:

CREATE DIRECTORY AOP_TEMPLATE AS '/home/oracle/aop_output';

Cloud (Dropbox, Google Drive, OneDrive, Amazon S3)

Save the output straight in a directory on the cloud.

Use following global variables to define the provider and location.

  • g_cloud_provider (dropbox, gdrive, onedrive, amazon_s3)
  • g_cloud_location (directory, or bucket with directory on Amazon)
  • g_cloud_access_token (oauth token)

Defined in Post Process (e.g. Printer)

Use when an external post-process command is specified and the output is only needed to be passed to that process. For example when you want to print directly to a Printer or call an OS command.