Since the introduction of the new APEX “Blueprint” feature (currently only available on apex.oracle.com), we can create APEX applications using a “blueprint”. The blueprint can be supplied in JSON format which opens up a number of great possibilities. As far as I know, the Blueprint feature will be available (outside apex.oracle.com) in the upcoming 5.2 version.
This blog will outline the overall steps for autogenerating applications from models. Everything mentioned and used in this blog is available on Github:
APEX Blueprint enables generation of applications with a lot of add-on features (Access control, Help pages, Email templates…), but also the ability to pre-define the pages containing our table driven page components (grids, IR, forms…). Below is a snippet of what this looks like in the blueprint JSON:
So, now that we can auto-generate our APEX applications from JSON, all we need is to convert the processes from our design phase into JSON.
This is where the Oracle SQLDeveloper Datamodeller features comes in handy. The best kept secret of SQLDeveloper, is the wealth of features for designing both the data and process model. You can access “models” via the menu : “View -> Data Modeller -> Browser“. In my example I have designed the process model containing composite processes and primitive lower level processes which represent the functionality I want in my APEX application:
In order to map the model processes to APEX pages, I have added som JSON annotations into the process notes, like this:
The are many variations of these JSON properties in the new Blueprint format. The best way (for now) is to try out difference page types using the APEX Blueprint creation page, and then check out the JSON that is generated from this.
Once the process model is defined (drawn) in SQLDeveloper, it can be exported to a Reporting Scheme (a database schema of your choice, which will contain all the information from your design models). Here is a script for creating the schema for this:
Once the reporting schema is created, we can transfer all information from the SQLDeveloper models into this schema (DMRS_xxx tables). The export into the reporting schema is performed by:
To extract relevant information the these DMRS_xx tables, please see these database views which contains examples for extracting process and datamodel information:
create or replace view lc_processes as
select d.design_ovid, d.design_name, date_published, published_by, de.diagram_name, p.process_id, p.parent_process_id, p.process_name, p.process_number, p.process_type, t.text, t2.text note
from lc_designs d
join datamodel.DMRS_DIAGRAMS de on DE.DESIGN_OVID = D.DESIGN_OVID
join datamodel.dmrs_processes p on p.diagram_id = de.model_id and p.diagram_ovid = de.model_ovid
left join datamodel.dmrs_large_text t on t.ovid = p.process_ovid and t.type = 'Footnote'
left join datamodel.dmrs_large_text t2 on t2.ovid = p.process_ovid and t2.type = 'Note';
(Remember to grant SELECT on the DMRS tables you want to query from your application schema).
Now that the model information is available we can create some PL/SQL which extracts the model information and generates JSON in the format that the APEX Blueprint expects. In the Github project (apex directory) you will find a application called:
The application will generate Blueprint JSON for the model design of your choice using this PL/SQL package (procedure generate_blueprint_json). Basically this PL/SQL procedure is traversing the processes from the reporting schema and generating JSON (using APEX_JSON), mimicking the JSON which is generated by the APEX Blueprint generator (apex.oracle.com -> Application Builder -> Create -> Blueprint).
Pasting the generated JSON output into APEX Blueprint will create the starting point for you application directly. Here is an example:
Even though it will probably never be possible to generate the complete application based on a process model (and some annotations in this), it gives us a pretty good starting point, having the menu structure and basic pages generated for us. This lowcode approach means we get the following APEX elements without even starting the APEX Application Builder:
- Navigation Menu
- Navigation Bar
- APEX Pages (Editable grids, Interactive reports, Forms, Calendars), already pre populated with the table and other options you have entered into the process description (JSON annotations).
- Much more – provided by APEX Blueprint
It will actually be enough if the application is a simple table maintaining application :-).
I wonder if the future will bring even more SQLDeveloper Modeller / APEX integration. One can only hope.
/Martin B. Nielsen