Automatically Generating APEX Pages – 2
My method involved the following steps :
Create an APEX report page as normal using the designer which would have all the features for all future report pages. Here is an example report.
NB. There are additional regions that are implemented on a global page but which also use the metadata tables.
Export the page and examine the calls to wwv_flow_api – match them to the components on the page.
Add tables and columns to my meta data model which will hold as many parameters as necessary for the calls to wwv_flow_api. The diagram shows a very simplified schematic of my metadata and how it relates to either the generator, the actual report at runtime (or both).
Create a “generator” package in pl/sql that will call the necessary wwv_flow_api procedures with my metadata providing the parameter values. If I later I need to add any components on the page, I adjust my package code and rerun the generator (for one or more pages). If I change the values in the metadata – I just rerun the generator for the page that is affected.
In the generator I start with querying the apex tables for the relevant ID values, for example, I need to get the ID values for :
- templates (page, report, item) – these will be constant for the application
- authorization schemes
- components – next available IDs for region, report, item, dynamic action etc.
I also construct various parameter values from my metadata tables – such as the report source, LOVs for the relevant options on each page, format masks and column lists.
The package then constructs and issues calls to the wwv_flow_api passing in the above ID values and other parameters. In my generator I needed to use the following :
- Delete page : wwv_flow_api.remove_page (if re-generating an existing page)
- Create page : wwv_flow_api.create_page
- Region : wwv_flow_api.create_page_plug
- Report : wwv_flow_api.create_worksheet
- Report columns : wwv_flow_api.create_worksheet_column. My report page always uses a view as the basis of the report region so I can loop through the relevant rows in oracle data dictionary user_tab_columns to get the column information.
- Saved IR reports :wwv_flow_api.create_worksheet_rpt
- Saved IR reports : wwv_flow_api.create_worksheet_condition
- Saved IR reports : wwv_flow_api.create_worksheet_group_by
- Page items : wwv_flow_api.create_page_item
- Dynamic Action Events : wwv_flow_api.create_page_da_event
- Dynamic Actions : wwv_flow_api.create_page_da_action
- Processes :wwv_flow_api.create_page_process
- Buttons : wwv_flow_api.create_page_button
Create another APEX application to manage my metadata tables and to call the generator.