Automatically Generating APEX Pages – 1
In 2016 I was asked to “do some stuff” with my company’s data. I chose to use Oracle APEX because
- Its free (the company is already using Oracle database)
- its easy to use (the company staff had limited Oracle skills)
Apart from that, APEX is what I do and so i had been employed specifically to introduce it into the company.
The existing MI “suite” consisted of a jumble of overnight extracts of data, each output from a simple stored procedure ( cursor definition and a loop to write each row to a csv file). There were about 270 of these extracts running daily, weekly or monthly. Everything after that point was done in … dare I say it .. Excel. There was no monitoring of report usage although the csv files were subject to windows access permissions.
I decided to create a single APEX application for reporting which would comprise interactive report pages – one equivalent to each csv extract. Nothing too unfamiliar that it that would scare off the end users. It would be straightforward to convert the existing data extracts into apex interactive reports (IRs) – use the existing extract definition from each stored procedure’s cursor declaration to create a view and base the IR on the view. Job done. Or not.
After coming up with a nice interactive report page and associated features for one of the extracts, I realised that creating over 200 of these bad boys would be a bit tedious. I have a DBA background and have always been a big fan of automating as much as possible – using APEX of course – so I knew there had to be an easier way.
Have you ever looked at an APEX export file? Really looked at it not just fudging the security group ID? When you install a page export or run the export file in SQLPlus or SQLDeveloper, the APEX page is created really quickly- so that was the approach I took – create a generator that mimics what the export file does. All I needed to do was to create some stored code of my own to do this. That is :
- Delete the page if it exists
- Create a page
- Create some regions
- Create items
- Create Interactive Reports
- Create Dynamic Actions
I created a set of tables to store the metadata – at the simplest this would be report and page information :page number, page name, view on which the IR is based and also a “generator” package which calls the apex wwv_flow_api packaged procedures. Unfortunately this package is undocumented but not difficult to fathom.
All the security and conditional logic is done by passing parameters to the wwv_flow_api procedures – the values for the parameters are supplied by the metadata tables. In addition the application uses a dynamic navigation menu (again based on my metadata tables) and a number of page zero regions with content also defined in the metadata.
It was very easy to create an APEX “toolkit” application to manage all this metadata with a “big green button” to generate a page in the reporting application.
Two years later and the Toolkit has grown to support additional meta data allowing the generator to include (among other things) alternative report definitions, sub-reports based on cubes, modal page links, maps and annotations. More of this later.
To add a new page to the reporting application, “developers” just have to write the SQL to define the view, fill in some metadata and click the big green button. They don’t need to know APEX or have access to the Designer.
Because the same components are generated each time, all the report pages have the same layout, look and feel regardless of who created them – the standard “template” is built into the generator. All reports therefore present a familiar interface to the end users – all of whom are new to APEX applications.
If a new feature is needed, for example I found the floating scrollbar plugin was very useful, I just modified the generator code and re-generated all the pages with a loop through my metadata. Imagine implementing a plugin based DA on 270 pages without losing the will to live.
I had been wary of using the undocumented packages but I have had no issues whatsoever. I originally implemented this method in APEX 4.2 and it is now at APEX 18.1 and has not needed any changes to it throughout those upgrades other than the APEX schema name