This application is an experimental project dedicated to developers who need to build sql query generating XML.
In addition, the application generates a default xslt FOP template which can be used by a FOP server.
Download on Github
For generating pdf, we must provide a valid Report server endpoint with FOP installed on tomcat.
I used the apex5_fop project from mikhailidim available on github.
That can be https://github.com/darklordgrep/APEX-FOP/blob/main/apex_fop.jsp as well, which has been published recently in march 2023.
Usage
We choose a table or a view and some general rendering options.
then, FOP LAB follows all the Foreign Keys links in oder to generate an sql query and a default xslt layout.
If no FK are retrieved, only the master table is printed. In others cases, the master is printed in form display and all the chlid tables are displayed as xslt tables.
Ie: if we choose EMP table, the « managed people » are displayed as a grid under each master emp.
xslt templates are generated from xslt fragments which can be customized directly in the application.
The application is mainly intended to generate a sql query which generates in turn some xml stuff.
Because wrinting this kind of query is cumbersome, FOP LAB can be a good friend.
If we need to customize content of a child, the supported metthod is to create a view with a name identical to the child table and a suffix ‘_FOP_V’. In this case the view supersedes the original table.
IN the following exemple, we use ORDERS_ITEMS_FOP_V instead ORDERS_ITEMS to display aggregates (Total) and retrieve the product name.
It’s needed to add a Foreign key with ‘DISABLED’ status for linking the view with the ORDERS table.
Exemple of generated Sql Query
select "ORDERS"
from
(SELECT XMLSERIALIZE(
CONTENT XMLELEMENT("ORDERS",
XMLELEMENT("HEADER", XMLELEMENT("report_date", TO_CHAR(SYSDATE,'Day DD Month YYYY')),
XMLELEMENT("TITLE", ''),
XMLELEMENT("DD", TO_CHAR(SYSDATE,'Day DD')),
XMLELEMENT("D", TO_CHAR(SYSDATE,'DD')),
XMLELEMENT("DAY", TO_CHAR(SYSDATE,'Day')),
XMLELEMENT("MONTH", TO_CHAR(SYSDATE,'Month')),
XMLELEMENT("YEAR", TO_CHAR(SYSDATE,'YYYY'))
),
XMLAGG (
XMLELEMENT("ORDERS_REC",
XMLFOREST (
e.ORDER_ID AS "ORDER_ID",
e.ORDER_DATETIME AS "ORDER_DATETIME",
e.CUSTOMER_ID AS "CUSTOMER_ID",
e.ORDER_STATUS AS "ORDER_STATUS",
e.STORE_ID AS "STORE_ID",
CUSTOMERS.CUSTOMER_ID AS "CUSTOMERS_CUSTOMER_ID",
CUSTOMERS.FULL_NAME AS "CUSTOMERS_FULL_NAME",
CUSTOMERS.EMAIL_ADDRESS AS "CUSTOMERS_EMAIL_ADDRESS",
STORES.STORE_ID AS "STORES_STORE_ID",
STORES.STORE_NAME AS "STORES_STORE_NAME",
STORES.WEB_ADDRESS AS "STORES_WEB_ADDRESS",
STORES.PHYSICAL_ADDRESS AS "STORES_PHYSICAL_ADDRESS",/* ------ ORDER_ITEMS_FOP_V / SYS_C0064516 ------ */
(select XMLAGG(
XMLELEMENT("ORDER_ITEMS_FOP_V_ORDERS_REC",
XMLFOREST(
ORDER_ITEMS_FOP_V.QUANTITY AS "QUANTITY",
ORDER_ITEMS_FOP_V.UNIT_PRICE AS "UNIT_PRICE",
ORDER_ITEMS_FOP_V.MNT AS "MNT",
ORDER_ITEMS_FOP_V.ORDER_ID AS "ORDER_ID",
ORDER_ITEMS_FOP_V.PRODUCT_ID AS "PRODUCT_ID",
ORDER_ITEMS_FOP_V.PRODUCT_NAME AS "PRODUCT_NAME"
)
)
)
from ORDER_ITEMS_FOP_V
where ORDER_ITEMS_FOP_V.ORDER_ID = e.ORDER_ID
) ORDER_ITEMS_FOP_V
)
)
)
)
) AS "ORDERS"
from "ORDERS" e ,
CUSTOMERS,
STORES
where rownum<100and
NVL(:PKID,e.ORDER_ID) = e.ORDER_ID
AND e.CUSTOMER_ID =CUSTOMERS.CUSTOMER_ID
AND e.STORE_ID =STORES.STORE_ID
)
