How does APEX store Report Queries & Layouts

We described in previous articles how to set up APEX printing with OC4J and how to design XLS-FO Report Layouts. But how does APEX store the Report Queries and Report Layouts sent as XML and XLST to the FO Processor to generate the PDF?

Report Queries

Report Queries are stored in the b view:

select * from APEX_040100.WWV_FLOW_SHARED_QUERIES

The main columns returned by this query:

  • QUERY_TEXT – contains the main source query defined for the Report Query
  • REPORT_LAYOUT_ID – contains the associated Report Layout. If there is no associated Report Layout, the column value will be null.
  • FORMAT – contains the download format, such as PDF or HTML.
  • XML_ITEMS – contains the list of session state items included in the report, separated by commas (Example: APP_USER)

When having multiple Report Query sources, the WWV_FLOW_SHARED_QUERIES will not show all of the query sources. To get all of the source queries for a Report Query use the WWV_FLOW_SHARED_QRY_SQL_STMTS view.

To retrieve all the source queries for a Report Query execute:

select * from APEX_040100.WWV_FLOW_SHARED_QRY_SQL_STMTS where FLOW_ID = <gflow_id> and SHARED_QUERY_ID = <query_id>;

 

Report Layouts

You can get the REPORT_LAYOUT_ID with a simple query:

select REPORT_LAYOUT_ID from APEX_040100.WWV_FLOW_SHARED_QUERIES

The Report Layouts are stored in the WWV_FLOW_REPORT_LAYOUTS:

select * from APEX_040100.wwv_flow_report_layouts

The main columns that match the Report Layout sections are:

  • PAGE_TEMPLATE – CLOB column that stores the Page Template section.
  • XSLFO_COLUMN_HEADING_TEMPLATE – Varchar2(4000) column that stores the Report Column Heading section.
  • XSLFO_COLUM_TEMPLATE – Varchar2(4000) column that stores the Column Template section.
  • XSLFO_COLUMN_TEMPLATE_WIDTH – Varchar2(4000) column that stores the Column Template Width section.

Important: The PAGE_TEMPLATE column is defined as CLOB and therefore can store information bigger than 32K, inserting a custom source code for the Page Template section that exceeds this size cannot be done the Application Builder because of the limitation that prevents submitting items with values  bigger than 32K.

Inserting big data into the PAGE_TEMPLATE column directly, via custom SQL or PL/SQL code can be done if the current schema has the grant to update data into the WWV_FLOW_REPORT_LAYOUTS view.

Leave a Reply