The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

  • March 28, 2018

APEX 18.1 Early Adopter 2: REST Services and Plug-Ins

Carsten Czarski
Consulting Member of technical Staff

In previous blog postings, we introduced and explained the new REST and REST Enabled SQL capabilities in Application Express 18.1. APEX can access data not only from the local database, but also from remote Oracle databases (using REST Enabled SQL) and from arbitrary REST services. APEX components are just built on top of these remote data sources.

This blog posting will be about Plug-Ins. The new Application Express version enables remote databases and REST services not only for standard APEX components (like reports or charts); Plug-In developers can leverage this feature as well.

The most important bits for Plug-In developers are the APEX_EXEC PL/SQL package (Documentation) and the Region supports different Data Sources Plug-In attribute. A Plug-In is able to deal with local SQL, remote SQL and REST services, when this attribute is enabled and when the Plug-In collects all its data only with the APEX_EXEC package.  

So let's start building a Plug-In.  Navigate to Shared Components, then to Plug-Ins and click the Create button. Create a new Plug-In from scratch. In the following screen, provide a display and an internal name and specify that this will be a Region Plug-In. Click the Create Plug-In button to create an (empty) Plug-In.

Then, navigate to the Standard Attributes section and enable the following items:

  • Region Source supports different Data Sources
  • Has "Page Items to Submit" Attribute
  • Has "No Data Found Message" Attribute
  • Has "Escape Special Characters" Region Attribute

Also click the Return to page checkbox in the upper right corner of the screen. That will lead the browser to stay on this page when saving changes with the Apply Changes button (which you should do now). Your changes will be saved, but you will not leave the Plug-In edit page.

Use the below code as the Plug-Ins PL/SQL Code (here is the complete code).

function handleZero(p_number in number) return number is
    return case when p_number = 0 then 1 else p_number end;
end handleZero;

function calculate_color(
    p_min_col varchar2,
    p_max_col varchar2,
    p_min_val number,
    p_max_val number,
    p_val     number
) return varchar2 is 
    l_min_red pls_integer := to_number(substr(p_min_col, 2, 2), 'XX');
    l_max_red pls_integer := to_number(substr(p_max_col, 2, 2), 'XX');
    l_min_blu pls_integer := to_number(substr(p_min_col, 6, 2), 'XX');
    l_max_blu pls_integer := to_number(substr(p_max_col, 6, 2), 'XX');
    l_min_gre pls_integer := to_number(substr(p_min_col, 4, 2), 'XX');
    l_max_gre pls_integer := to_number(substr(p_max_col, 4, 2), 'XX');

    l_red     pls_integer;
    l_gre     pls_integer;
    l_blu     pls_integer;
    l_red := l_min_red + ((l_max_red - l_min_red) * ((p_val - p_min_val) / handleZero(p_max_val - p_min_val)));
    l_gre := l_min_gre + ((l_max_gre - l_min_gre) * ((p_val - p_min_val) / handleZero(p_max_val - p_min_val)));
    l_blu := l_min_blu + ((l_max_blu - l_min_blu) * ((p_val - p_min_val) / handleZero(p_max_val - p_min_val)));
    return '#'||ltrim(to_char(l_red, '0X'))||ltrim(to_char(l_gre, '0X'))||ltrim(to_char(l_blu, '0X'));
end calculate_color;

procedure generate_cloud(
    p_region              in apex_plugin.t_region
) is 
    type t_label_cloud_row is record(
        label  varchar2(32767),
        value  number );

    type t_label_cloud_tab is table of t_label_cloud_row index by pls_integer;

    l_label_col      apex_application_page_regions.attribute_01%type := p_region.attribute_01; 
    l_value_col      apex_application_page_regions.attribute_02%type := p_region.attribute_02; 
    l_maximum_rows   apex_application_page_regions.attribute_03%type := p_region.attribute_03; 

    l_mincolor       apex_application_page_regions.attribute_04%type := p_region.attribute_04;
    l_maxcolor       apex_application_page_regions.attribute_05%type := p_region.attribute_05;
    l_minsize        apex_application_page_regions.attribute_06%type := p_region.attribute_06;
    l_maxsize        apex_application_page_regions.attribute_07%type := p_region.attribute_07;

    l_values         t_label_cloud_tab;

    l_columns        apex_exec.t_columns;
    l_context        apex_exec.t_context;

    l_idx            pls_integer := 0;
    l_max_value      number := 0;
    l_min_value      number := 0;

    l_label_col_no   pls_integer;
    l_value_col_no   pls_integer;

    l_label          varchar2(32767);

    l_context := apex_exec.open_query_context(
        p_columns           => l_columns,
        p_max_rows          => l_maximum_rows );

    l_label_col_no := apex_exec.get_column_position( l_context, l_label_col );
    l_value_col_no := apex_exec.get_column_position( l_context, l_value_col );

    while apex_exec.next_row( p_context => l_context ) loop

        l_idx := l_idx + 1;

        l_label := apex_exec.get_varchar2( l_context, l_label_col_no );
        l_values( l_idx ).value := apex_exec.get_number( l_context, l_value_col_no );

        l_values( l_idx ).label := case when p_region.escape_output
                                        then apex_escape.html( l_label )
                                        else l_label end;

        if l_values( l_idx ).value < l_min_value then
            l_min_value := l_values( l_idx ).value;
        end if;

        if l_values( l_idx ).value > l_max_value then
            l_max_value := l_values( l_idx ).value;
        end if;

    end loop;

    apex_exec.close( l_context );

    if l_values.count > 0 then
        l_idx := l_values.first;
        while l_idx is not null loop

                '<span class="labelcloud-plugin-label" style="white-space: nowrap; color: '||
                calculate_color( l_mincolor, l_maxcolor, l_min_value, l_max_value, l_values( l_idx ).value )||
             '; font-size: '||
               l_minsize + (
                 ((l_values( l_idx ).value - l_min_value) / handleZero(l_max_value - l_min_value)) * (l_maxsize - l_minsize)
             l_values( l_idx ).label ||
            l_idx := l_values.next( l_idx );
        end loop;
        sys.htp.p( p_region.no_data_found_message);
    end if;
    when others then
        apex_exec.close( l_context );
end generate_cloud;

function apexplugin_render_ajax (
    p_region              in apex_plugin.t_region,
    p_plugin              in apex_plugin.t_plugin
) return apex_plugin.t_region_ajax_result
        p_region => p_region );
    return null;
end apexplugin_render_ajax; 

function apexplugin_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 
    l_ajax_result apex_plugin.t_region_ajax_result;

        '<div id="labelcloud_plugin_' ||
        apex_plugin_util.escape(p_region.static_id, true) ||
        '" class="labelcloud-plugin">' );
    l_ajax_result :=  apexplugin_render_ajax(p_region, p_plugin);

        'apex.jQuery("#'||apex_javascript.escape(p_region.static_id)||'")' || 
            '.bind("apexrefresh", function() {labelCloudPluginRefresh('||
                apex_javascript.add_value(p_region.static_id, true)||
                apex_javascript.add_value(apex_plugin.get_ajax_identifier, true)||
                apex_javascript.add_value(p_region.ajax_items_to_submit, false)||');});');

    return null;
end apexplugin_render; 

This code uses the APEX_EXEC package to get data from either the local or remote database, or a REST service. Some interesting snippets within this code are ...

  • Invoke the actual SQL query or fetch data from the REST service:
        l_context := apex_exec.open_query_context(
            p_columns           => l_columns,
            p_max_rows          => l_maximum_rows );
    The APEX_EXEC.OPEN_QUERY_CONTEXT (Documentation) uses the current region meta data in order to determine where to get the data from. When the region uses a SQL query to be executed on the local database, APEX_EXEC just does this. However, when the region has been configured to use a Web Source ( REST Service ), then APEX_EXEC.OPEN_QUERY_CONTEXT will invoke the REST service, parse the returned data and provide rows and columns to the Plug-In.
    OPEN_QUERY_CONTEXT takes even more parameters. Plug-In developers can, for instance, also supply a "wrapping SQL query" to post-process the data. The procedure signature is as follows:
    function open_query_context (
        p_columns               in t_columns    default c_empty_columns,
        p_filters               in t_filters    default c_empty_filters,
        p_order_bys             in t_order_bys  default c_empty_order_bys,
        p_first_row             in pls_integer  default null,
        p_max_rows              in pls_integer  default null,
        p_total_row_count       in boolean      default false,
        p_total_row_count_limit in pls_integer  default null,
        p_select_all_query_cols in boolean      default false,
        p_sql_parameters        in t_parameters default c_empty_parameters ) return t_context;
  • Loop over the result set:
        while apex_exec.next_row( p_context => l_context ) loop
            l_idx := l_idx + 1;
            l_label := apex_exec.get_varchar2( l_context, l_label_col_no );
        end loop;
    Using the APEX_EXEC.NEXT_ROW function, the Plug-In will iterate over the rows, returned by the SQL query or REST service. Individual column values are being fetched with the GET_VARCHAR2, GET_NUMBER and other functions within the APEX_EXEC package. NEXT_ROW will return true as long as the data source returns rows. 
    When REST services powered by ORDS (Oracle REST Data Services) are being used, NEXT_ROW will even trigger fetching the next page from the server - transparently to the Plug-In.
  • Close the Query Context object and release resources
        apex_exec.close( l_context );
    When all rows have been processed, release all resources with APEX_EXEC.CLOSE (Documentation). This will also close cursors being opened during query execution.
  • When something goes wrong: Don't forget the exception handler
        when others then
            apex_exec.close( l_context );
    Since APEX_EXEC.CLOSE also releases open cursors, it's important to have it called always, also when exceptions occur within the Plug-In PL/SQL code. So, make sure to have an exception handler with at least APEX_EXEC.CLOSE in it. You may simply raise the exception afterwards.

The rest of the PL/SQL code is specific to the Plug-In domain (which is rendering data as a "label cloud"). Feel free to browse through the code in order to understand what will happen. When done, copy & paste the code into the PL/SQL Code code editor and click Apply Changes. Since you have enabled the Return to page checkbox in the upper right corner, you will stay on the Plug-In page.

In the Callbacks section, enter the procedure names from the PL/SQL code as the AJAX and Render function names, as follows:

  • apexplugin_render is the Render Procedure/Function Name
  • apexplugin_render_ajax is the AJAX Procedure/Function Name


Next, it's time to configure the Plug-Ins Custom Attributes. In the Custom Attributes Section, click the Add Attribute button to create a new attribute.

Configure the following attributes - make sure to set Required to Yes for all of them.

  1. Label Column of the Region SQL Statement Column type. For a label column, you can allow Varchar2, Number, Clob and all Date and Timestamp data types.
  2. Value Column of the Region SQL Statement Column type. Only the Number data type should be allowed here.
  3. Maximum Rows of the Number type. Use a default value of 1000 and allow 5 digits to enter.
  4. "Minimum" Color (Hex) of the Text type. Allow 10 characters to enter. Default 808080.
  5. "Maximum" Color (Hex) of the Text type. Allow 10 characters to enter. Default FF0000.
  6. Minimum Size of the Number type. Allow 5 digits to enter; default 10.
  7. Maximum Size of the Number type. Allow 5 digits to enter; default 20.

When done, the Custom Attributes section should look as follows.

Finally, we need to add a bit of Javascript and CSS code to the Plug-In. First, store the following code in a file named labelcloudajax.js and upload.

function labelCloudPluginRefresh( pRegionId, pPluginName, pItemList ) {
    var lItemList;

    if ( pItemList && pItemList != "" ) {
        lItemList = pItemList.split(",").reduce( function( r, i ) { 
                                                                      return r + "#" + i.replace( /^#?/, "" ) + ",";
                                                 "" )
                                        .replace( /,$/, "" );

            pageItems: lItemList
            dataType: "text",
            success: function( pData ) { 
                                           $x("labelcloud_plugin_"+pRegionId).innerHTML = pData; 

Then, store this CSS code as labelcloudcss.css and upload it.

.labelcloud-plugin { text-aligncenter }
.labelcloud-plugin-label { margin-left10pxmargin-right10pxdisplayinline-block;}

In the File URLs to Load section, reference the uploaded files as follows:

  • #PLUGIN_FILES#labelcloudcss.css in the Cascading Style Sheet text area.
  • #PLUGIN_FILES#labelcloudajax.js in the JavaScript text area.

And that's it. After finally saving all changes we can start using the Plug-In on a new Application Express Page. Create a new, blank page and open Page Designer.

From the Region Gallery below, drag a region of the new A brand new Label Cloud Plug-In to the Content Body section of the page.

Let's first use the well-known EMP table as the data source for the Plug-In. Configuring Region Attributes (Section Source) is very straightforward:

  1. Location: Local database
  2. Type: Table / View
  3. Table Owner: Parsing Schema
  4. Table Name: EMP

In the navigation tree on the left side, click the Attributes node of the new region (which should be marked red). Configure ENAME as the Label Column and SAL as the Value Column. Leave the other attributes at their defaults.

When done, run your page. The result should look as follows:

Now let's use this Plug-In for a REST service. Navigate to Shared Components and configure the "Github Repositories" Web Source Module, as described in the  Application Express Early Adopter: REST Services! blog posting.

Then navigate back to Page Designer and drag another region of the Plug-In type to your page. In the Source section of the region attributes, now choose Web Source as Location and Github Repositories as the Web Source Module.

In the Plug-In attributes, configure NAME as the Label Column and WATCHERS_COUNT as the Value Column (of course, you can choose other columns as well).

Save your changes and run the page.

Your page now contains two Label Clouds - both are rendered with the same APEX Plug-In. As APEX standard components, your Plug-Ins can work on remote and local data. To leverage REST Enabled SQL, configure a REST Enabled SQL service, as described in the  Application Express Early Adopter: REST Enabled SQL! blog posting and configure the Plug-In accordingly. One Plug-In implementation can work on different data sources.

Back to blogs.oracle.com/apex