Create a region plugin for Oracle Application Express

This article is intended for all those who work closely with Oracle Application Express (in simple terms - APEX, or even just apex). As well as those who have heard something and are thinking of starting to use it in their work. After reading the article, I hope you will have the desire to do so.

Introductory information

It is assumed that the reader is familiar (or will get to know soon after reading) at least in general terms with the following things:

• What is SQL and PL / SQL, and how do they differ from each other
• What objects are in Oracle DBMS and why are they needed (tables, packages, view)
• The main components of the apex application are: page, region, item, process, etc., how to create and delete them
• Fundamentals of apex administration: how to create a table, package, view, and how to make them available for your application in Oracle
• How to use the value of the item as an SQL query parameter

There is nothing supercomplex in these points, all these things at a basic level can more or less be mastered for a month of unhurried work. The example is intended for the fifth version of the apex, which was released a little less than a year ago.

What is a region plugin in apex and what is needed for it

In the apex, you can create plug-ins of regions, items, processes, dynamic actions (Dynamic action) and authentication and authorization schemes. What is a plug-in region, I think, is intuitive. You are creating a new type of region that can do something that standard apex regions cannot do. Often the most difficult thing here is to come up with some really worthwhile idea for implementation. As an example for the article, I came up with a plugin for creating a rubrication, the source of inspiration was the alphabetic and subject indexes in the books and this page on the website htmlbook.ru. So let's get started.
')

Initial data

To begin with, we will create tables with the data. We will have a rubricator of the countries of the world selected by my left heel right before writing the article. Suppose we keep a list of countries and continents, as well as links to Wikipedia articles about these countries. We need to accurately and beautifully display this list on the screen, and so that they are divided into headings (headings will be the corresponding continents). The plugin will also have one custom property - the number of columns in which data will be displayed.

Plugin

The plugin is created in the apex application. Open the IDE, select the necessary application, go to the section "Shared Components", there we find the section "Other Components", and in it - "Plug-ins". We go to the plugins page, click "Create", the plug-in wizard is launched. Next in steps (there are only two): specify the method of creation - “From Scratch”, then specify the properties. You must specify the name (“Name”), the internal name (“Internal name”), the type of plug-in (“Type” - “Region”) and the type of supported applications (“Supported for”) - Desktop and Mobile (tick the necessary items) . Next, click "Create Plug-in". The plugin is created, now you need to configure it a bit. Go to the properties of the plug-in (to do this, click on its name in the list of plug-ins), in the section "Standard Attributes" we tick the box next to "Region Source is SQL Statement" and "Region Source Required". Go to the “Custom Attributes” section, click on “Add Attribute” (to add a property to set the number of columns in the output), fill in the following fields:

• "Scope" - "Component"
• "Attribute" - "1" (the ordinal number of custom attributes, below will be a little about what to do with this number then)
• "Display Sequence" - leave the default value of "10"
• “Label” - “Columns count” (this name will be displayed in the settings when we will create a region based on this plugin)
• "Type" - "Integer"
• "Supported for" - "Desktop"

A spoon of tar in custom attributes. There is one extremely unpleasant cant there: if you want to make a custom attribute of type “Select List”, make it neat. In the interface there are no buttons to delete individual entries, and also it is impossible to edit the return value. Here is a screenshot of this indecency:



As you can see, the "Delete" button is not there. And the “return value” field is uneditable. That is, if you make a mistake somewhere and do not notice right away, or decide to modify the list much, then you will have to delete the attribute and recreate it from scratch. Yes, and all your 20 entries for the drop-down list too.

Now about the most important feature of the plug-in: on the “Callbacks” tab, you must specify “Render Function Name” - the name of the PL / SQL function that will generate the HTML code for the region to be displayed by the browser. According to the documentation, the function must have the following signature:

 function <name of function> ( p_region in apex_plugin.t_region, p_plugin in apex_plugin.t_plugin, p_is_printer_friendly in boolean ) return apex_plugin.t_region_render_result 

By the way, in order to get a description of this function, you do not even have to go into the documentation, just click on the question in front of the “Render Function Name” field and copy the text from the built-in prompt. And since we started talking about documentation, the documentation for the APEX_PLUGIN and APEX_PLUGIN_UTIL packages is very useful to you for developing plug-ins.
The most mysterious thing about this function is that I have not yet managed to find the description of the result returned by the function and where it is used. You can simply return NULL, and everything will work.

Render function

The plugin is almost ready, now you need to make a rendering function. Create a function:

 function render ( p_region in apex_plugin.t_region, p_plugin in apex_plugin.t_plugin, p_is_printer_friendly in boolean ) return apex_plugin.t_region_render_result is begin return null; end; 

The code that generates HTML, as you may have guessed, should be placed between the "begin" and "return null;" lines.
To generate a region based on the result of the user's SQL query, this query must be retrieved and executed. Getting the request is simple: we read the description of the apex_plugin.t_region type and find out that the request text is stored in the p_region.source field. It remains only to run this query, but do not rush to grab EXECUTE IMMEDIATE, because here it will not help you! The fact is that the user in the request can specify the parameters that are associated with page fields by the apex engine. The apex engine can identify and fill them with data. It’s almost impossible to do this yourself, you’ll have to write your query parser. How then to execute the request? You can prevent the user from using queries with parameters, or you can delve into the depths of the documentation and find the APEX_PLUGIN_UTIL.GET_DATA family of functions (two GET_DATA functions and two GET_DATA2 functions, for details see the package documentation). These functions take as input the SQL code of the query, parse it, define the parameters, find the corresponding items on the page, etc. The result is returned as a collection, its description is on the same documentation page. The same p_region variable (which is a RECORD entry) contains attributes with the names attribute_01 ... attribute_25. These numbers correspond to the numbers of custom attributes specified in the "Attribute" field in the creation process.

And the last. HTML code is inserted into the page using the htp.p procedure (not to be confused with http!). That is the code

 htp.p('<b> !</b>'); 

Will display on the page the inscription "Hello!" In bold. The full function code for our plugin will be lower.

I want to try too!

A plugin with everything you need can be found here . Included are the following files:

• region_type_plugin_rubrikator.sql - plugin export file
• render_plugin_rubrikator.pls - package header with render function
• render_plugin_rubrikator body.pls - package body with a rendering function

Installing the plug-in (no need to perform if you have completed all the steps to create a plug-in from this article): go to the IDE, open the application you need, import the file with the plug-in (region_type_plugin_rubrikator.sql). Specify the File Type - Plug-in, click Next, Next, Install Plug-in.

Package installation: compile the package from the files render_plugin_rubrikator.pls and render_plugin_rubrikator body.pls.

The package contains a rendering function (render) and two procedures (prepare_demo and drop_demo), which, respectively, create and delete tables and view for demonstration (the code executed by the procedure prepare_demo is given at the beginning of the article; if you have already done it, call the procedure prepare_demo not need to). Execute the prepare_demo procedure after installation:

 begin render_plugin_rubrikator.prepare_demo; end; / 

Now create a new page, create two regions on it. Type the region, specify - Plug-ins, in the list of plug-ins, select "Rubrikator". As a data source, specify:

For the first region:

 select * from by_continent 

For the second:

 select * from by_alphabet 

Here, by_continent and by_alphabet are two views created by the prepare_demo procedure. In the current version of the plugin, the requirements for the source request are as follows: the first column should contain the headings of headings, the second - items within the headings, the third - links. If the link is NULL, the record will be shown on the page without the <a> tag.

Select the value of the Columns count field in the plugin to your liking. In my demo version will be 2 for the first and 3 for the second.
The result of the first query, for example:



And if you run the page, our plugin converts it to this form:

First region:



Second region:



Link to the page.