AOP Dynamic Action
Overview
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 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. E.g
{"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.
In case no data is returned, it most likely means that item PXX_ITEM value is not available in session state. In order to let AOP know about this value, please add the item in Affected Elements of the AOP DA.
Alternative you can change the PXX_ITEM attribute of Session State to "Per Session (Persistent)".
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.
Region(s): Classic Report, Interactive Report/Grid, SVG, Canvas, HTML, Other
Enter a comma separated list of static id of one of following region types:
- Classic Report
- Interactive Report
- Interactive Grid
- JET Chart
- Any other region (static html, div, plugin) which you want to include
e.g. my_classic_report,ir1,jet2,my_div
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.
To include an Interactive Grid as you see on the screen you use the tag {&interactive_<static_id>}.
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>}
To include the svg(s) in the div use {%region} and specify in the Custom Attributes of the region: aop-region-as="client_svg"
To include a canvas in the div use {%region} and specify in the Custom Attributes of the region: aop-region-as="client_canvas"
To include the html in the div use {_region} and specify in the Custom Attributes of the region: aop-region-as="server_html" or aop-region-as="client_html". AOP will translate the html into native Word styling either by passing the HTML defined in the Region Source (server_html) or defined after rendering on the page (client_html).
To include a screenshot of the div use {%region}, you don't have to specify anything or you can specify in the Custom Attributes of the region: aop-region-as="client_screenshot".
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
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
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
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
json_object)While using Native JSON DB (ex. json_object) inside a cursor, the column name should always start with apex_json_ (ex. 'apex_json_someName', 'apex_json_columnName').
Failing to do so, will restrict you from using any columns or keys of the JSON you are assigning.
AOP will create a new colum (replacing apexjson from the existing column name. For ex. apex_json_someName column name would be someName) with parsed JSON, whose keys can be used easily in the template.
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" ,
json_object(
'cust_first_name' value c.cust_first_name,
'cust_last_name' value c.cust_last_name,
'cust_city' value c.cust_city
) as "apex_json_name"
from aop_sample_customers c) as "customers"
from dual) as "data"
from dual;
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
If you are hitting a 4K limit in the Oracle APEX plug-in, use PL/SQL Function (returning SQL). See next for an example. Alternatively you can create one or more classic reports and add the SQL statements in there. Cursors are supported aswell in classic reports (but you don't need the filename from dual). You reference one or more static ids of the classic report. The classic report should have a condition; REQUEST = AOP, so it's only used for the source of AOP and not visible on the page.
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). Under Static ID in the Custom Attributes you can define how AOP should behave: aop-region-as="server_html / client_canvas / client_svg / client_html / client_screenshot". Depending this setting, AOP will render the HTML from the server or it will take from client the canvas, svg, html or take a screenshot of the region.
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.
Obfuscate data
Obfuscate data, so it's easy to send examples to others.
Init PL/SQL Code
You can define global variables of the aop_api_pkg in this area.
Available variables:
-- Global variables
g_output_filename varchar2(100) := null;
g_language varchar2(2) := 'en'; -- Language can be: en, fr, nl, de
g_rpt_header_font_name varchar2(50) := ''; -- Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163
g_rpt_header_font_size varchar2(3) := ''; -- 14
g_rpt_header_font_color varchar2(50) := ''; -- #071626
g_rpt_header_back_color varchar2(50) := ''; -- #FAFAFA
g_rpt_header_border_width varchar2(50) := ''; -- 1 ; '0' = no border
g_rpt_header_border_color varchar2(50) := ''; -- #000000
g_rpt_data_font_name varchar2(50) := ''; -- Arial - see https://www.microsoft.com/typography/Fonts/product.aspx?PID=163
g_rpt_data_font_size varchar2(3) := ''; -- 14
g_rpt_data_font_color varchar2(50) := ''; -- #000000
g_rpt_data_back_color varchar2(50) := ''; -- #FFFFFF
g_rpt_data_border_width varchar2(50) := ''; -- 1 ; '0' = no border
g_rpt_data_border_color varchar2(50) := ''; -- #000000
g_rpt_data_alt_row_color varchar2(50) := ''; -- #FFFFFF for no alt row color, use same color as g_rpt_data_back_color
Examples
aop_api_pkg.g_output_filename := 'output';
aop_api_pkg.g_output_filename := v('P1_FILENAME');
aop_api_pkg.g_rpt_header_font_size := '12';
aop_api_pkg.g_rpt_header_font_size := '12';
aop_api_pkg.g_prepend_files_sql := q'[select filename, mime_type, template_blob as file_blob from aop_template where filename like 'PREPEND%' order by filename]';
aop_api_pkg.g_append_files_sql := q'[select filename, mime_type, template_blob as file_blob from aop_template where filename like 'APPEND%' order by filename]';
You can set the filename by specifying the global variable: g_output_filename. In the cursor you also find a filename, this is used when multiple files are generated in a single request and a zip is returned.
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 - APEX item with value for the output type (Ex:
docx,xlsx,pptx,pdf,html,md,txt,rtf,csv,pdf,PDF,docm,xlsm,pptm,ics,ifband so on.)
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)
Download by the browser.
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.
Output to Table Using Procedure
With AOP, you can store output directly in a database table using a procedure. This approach calls the procedure in a specific format, making it ideal when local file storage isn’t needed. For example, if you prefer to save the output directly to a database table instead of your hard drive, this feature offers an efficient solution.
Example: You can refer to the
Output to Tableexample from the AOP sample app.
Note: This is only possible if your database table is in the same workspace.