SQL Structure
Cursor Syntax
Please refer to the image below containing SQL query and matching template.
AOP Plug-in supports PL/SQL cursor expressions which can be used in your templates as "data loops".
The left side corresponds to the SQL query that we used in our demo example. The right side is our Word template.
In a SQL query you will notice three inner cursors called data, orders and product.
The idea is for each customer to find orders and products and display it as an invoice letter.
The data cursor is a standard part of the AOP JSON structure and it needs to be present in all SQL queries. At the same time you do not have to specify it as a loop in your template.
Two other cursor names can be of your preference, as long as they are declared in your template.
Why do we use a cursor syntax?
This is the way AOP engine merges your data with your templates.
In our template example you want to display customer details (Data cursor) and then display all their orders (Orders cursor) with products (Product cursor) so each represents a separate cursor.
This means that we want to be able to create a loop for orders and a separate loop for products. This is exactly what we have done in this template.
The way AOP template loop works is by using {#loop element}….{/loop element} notation. In the template notice that we are using the following:
"Thank you for shopping. We have following products reserved for you:
{#orders}{#product}
- {product_name}{/product}
The total amount of your order is €{order_total}.
{/orders}"
First open Orders loop then open Product loop then doing a bullet numbering for all products. Before closing the Product loop and then closing the Orders loop.
Once you create your SQL query it is very easy to implement it in your template following few simple tagging rules depending on a template type you are creating.
Data Type/Data Source in DA and Process Plugin
AOP (DA and Process Plugin) provides several ways to provide data that is used while document genartion, which are listed below.
- SQL
- PL/SQL Function (returning SQL)
- PL/SQL Function (returning JSON)
- Region(s): Classic Report, Interactive Report/Grid, SVG, Canvas, HTML, Other
- URL (returning JSON)
- XML (data part)
- JSON (data part)
- JSON (REST, GraphQL, Raw)
- None
AOP expects the JSON structure of the data. Thus, These all options should return the JSON structure. Simple examples and description are available in the Help section in the APEX when Data Source field is clicked.
Some of the example of its usage are described below.
1. SQL
A SQL statement is the easiest to use, either by using the cursor syntax or native JSON database functionality. Enter a select statement in which you can use a cursor to do nested records. You can also use SQL that generates JSON.
- Images need to be base64 encoded. eg: APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image)
- Use "" as alias for column names to force lower case column names.
- You can also use/reference items or bind variables using :ITEM 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)".
Data Source
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"
from demo_customers c
where c.customer_id = :P1_CUST_ID
) 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.
2. 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.
- PL/SQL returning SQL
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;
3. 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.
- PL/SQL returning JSON
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
4. Region(s): Classic Report, Interactive Report/Grid, SVG, Canvas, HTML, Other
When Region(s): Classic Report, Interactive Report/Grid, SVG, Canvas, HTML, Other is selected, new field Region Static Id(s) will appear, where you can define one or more Static Id(s) of the report region.
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
Static ids should be separated by a comma. e.g. ir1,ir2
e.g. my_classic_report,ir1,jet2,my_div
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.
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".
5. 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.
In the data source, directly provide the URL which returns a JSON object with following format: { "filename": "file1", "data":[{...}] }
- Data Source
https://www.apexofficeprint.com/ords/aop/get_data/1
6. XML (data part)
Data in XML format. No need to specify files or filename.
7. JSON (data part)
Specify the data in JSON format.
You can directly provide the data JSON part without filename and data keys. When filename and data are not provided in the first hierarchy, the provided JSON is directly inserted inside data key.
- Data Source
[
{
"filename": "file1",
"data": [
{
"customers": [
{
"cust_first_name": "William",
"cust_last_name": "Hartsfield",
"cust_city": "Atlanta",
"orders": [
{
"order_total": 1640,
"order_name": "Order 1",
"product": [
{
"product_name": "Blouse",
"quantity": 4,
"unit_price": 60
},
{
"product_name": "Skirt",
"quantity": 4,
"unit_price": 80
},
{
"product_name": "Ladies Shoes",
"quantity": 4,
"unit_price": 120
},
{ "product_name": "Bag", "quantity": 4, "unit_price": 125 },
{
"product_name": "Wallet",
"quantity": 2,
"unit_price": 50
}
]
},
{
"order_total": 730,
"order_name": "Order 2",
"product": [
{
"product_name": "Blouse",
"quantity": 4,
"unit_price": 60
},
{
"product_name": "Skirt",
"quantity": 3,
"unit_price": 80
},
{ "product_name": "Bag", "quantity": 2, "unit_price": 125 }
]
}
]
},
{
"cust_first_name": "Edward",
"cust_last_name": "Logan",
"cust_city": "East Boston",
"orders": [
{
"order_total": 1515,
"order_name": "Order 1",
"product": [
{
"product_name": "Jacket",
"quantity": 3,
"unit_price": 150
},
{
"product_name": "Ladies Shoes",
"quantity": 3,
"unit_price": 120
},
{ "product_name": "Bag", "quantity": 3, "unit_price": 125 },
{
"product_name": "Mens Shoes",
"quantity": 3,
"unit_price": 110
}
]
},
{
"order_total": 905,
"order_name": "Order 2",
"product": [
{
"product_name": "Business Shirt",
"quantity": 2,
"unit_price": 50
},
{
"product_name": "Trousers",
"quantity": 2,
"unit_price": 80
},
{
"product_name": "Blouse",
"quantity": 2,
"unit_price": 60
},
{
"product_name": "Skirt",
"quantity": 2,
"unit_price": 80
},
{ "product_name": "Belt", "quantity": 3, "unit_price": 30 },
{ "product_name": "Bag", "quantity": 1, "unit_price": 125 },
{
"product_name": "Wallet",
"quantity": 3,
"unit_price": 50
}
]
}
]
}
]
}
]
}
]
8. JSON (REST, GraphQL, Raw)
For this options, you need to enter the endpoint and datasource in the following JSON structure. AOP Server performs the request internally based on the datasource options.
- Data Source
{
"filename": "womensitems.docx",
"data": {
}
},
{
"filename": "womensitems.docx",
"datasource":"graphql",
"query":"query {shows { name year genres episodes { name poster description } }}",
"endpoint":"https://api.graph.cool/simple/v1/cjwj18ejn5n4x01593uoft50b",
"headers":[{"Custom-Auth-Token":"xysazxklj4568asdf46a5sd4f"}]
"auth":"" //Basic authentication i.e. 'user:password' to compute an Authorization header.
},
{
"filename": "womensitems.docx",
"datasource":"rest",
"method":"GET",
"body":"...",
"endpoint":"https://www.example.com/qr/",
"headers":[{"Content-Type":"application/json"},{"Custom-Auth-Token":"xysazxklj4568asdf46a5sd4f"}]
"auth":"" //Basic authentication i.e. 'user:password' to compute an Authorization header.
}
The files part of the AOP structure will be filled in with the value specified here.
9. None
To only append or prepend files or to work with PDFs, set the data type to none.
JSON Duality View
Oracle APEX running on Oracle Database 23c and greater can use JSON Duality View for the selection of data.
Example of creation of JSON Duality View
This example uses the table AOP_SAMPLE_PRODUCT_INFO, AOP_SAMPLE_ORDER_ITEMS, AOP_SAMPLE_ORDERS and AOP_SAMPLE_CUSTOMERS which are available in the aop_sample_data_pkg.sql provided with the AOP Plugins and packages.
CREATE OR REPLACE FORCE EDITIONABLE JSON RELATIONAL DUALITY VIEW "CUST_ORDER_JDV" AS
SELECT JSON {
'_id' : c.customer_id,
'cust_first_name' : c.cust_first_name,
'cust_last_name' : c.cust_last_name,
'cust_city' : c.cust_city,
'orders' : [
SELECT JSON {
'order_name' : o.order_id,
'product' : [
SELECT JSON {
'order_item_id': i.order_item_id,
'quantity' : i.quantity,
'unit_price': i.unit_price,
unnest
(
SELECT JSON {
'product_name' : p.product_name,
'product_id' : p.product_id
}
from AOP_SAMPLE_PRODUCT_INFO p where
i.product_id = p.product_id
)
}
FROM aop_sample_order_items i
WHERE o.order_id = i.order_id
]
}
FROM aop_sample_orders o
WHERE c.customer_id = o.customer_id
]
}
FROM aop_sample_customers c;
Data Selection using JSON Duality View
For the data selection using the CUST_ORDER_JDV, following SQL can be used:
- SQL
- JSON
select
json_arrayagg(
json_object(
'filename' value 'file1',
'data' value (SELECT * FROM cust_order_jdv v WHERE v.DATA."_id" = 1)
)
) as aop_json
from dual;
[
{
"filename": "file1",
"data": {
"_id": 1,
"cust_first_name": "John",
"cust_last_name": "Dulles",
"cust_city": "Sterling",
"orders": [
{
"order_name": 2,
"product": [
{
"order_item_id": 103,
"quantity": 3,
"unit_price": 50,
"product_name": "Business Shirt",
"product_id": 1
},
{
"order_item_id": 104,
"quantity": 3,
"unit_price": 80,
"product_name": "Trousers",
"product_id": 2
},
{
"order_item_id": 105,
"quantity": 3,
"unit_price": 150,
"product_name": "Jacket",
"product_id": 3
},
{
"order_item_id": 106,
"quantity": 3,
"unit_price": 60,
"product_name": "Blouse",
"product_id": 4
},
{
"order_item_id": 107,
"quantity": 3,
"unit_price": 80,
"product_name": "Skirt",
"product_id": 5
},
{
"order_item_id": 108,
"quantity": 2,
"unit_price": 120,
"product_name": "Ladies Shoes",
"product_id": 6
},
{
"order_item_id": 109,
"quantity": 2,
"unit_price": 30,
"product_name": "Belt",
"product_id": 7
},
{
"order_item_id": 110,
"quantity": 4,
"unit_price": 125,
"product_name": "Bag",
"product_id": 8
},
{
"order_item_id": 111,
"quantity": 2,
"unit_price": 110,
"product_name": "Mens Shoes",
"product_id": 9
},
{
"order_item_id": 112,
"quantity": 2,
"unit_price": 50,
"product_name": "Wallet",
"product_id": 10
}
]
}
],
"_metadata": {
"etag": "A2299DC5AE4553A3C6F3347202B2CB1F",
"asof": "000000006B8AC837"
}
}
}
]
Reference on JSON Duality View: