How to Build APEX Plug-ins in PL/SQL Using APEX_PLUGIN and APEX_PLUGIN_UTIL

A plug-in adds a new item type, region type, dynamic action, process type, authentication or authorization scheme, REST Data Source type, or AI tool to Oracle APEX. Its PL/SQL code is a set of callbacks (render, Ajax, validation, execution, and others) that APEX calls with records describing the component and the plug-in. APEX_PLUGIN declares those records, and APEX_PLUGIN_UTIL provides the helpers most callbacks need.

This guide walks through both packages with tested examples: the records a callback receives, printing item HTML safely, answering an Ajax call with an LOV as JSON, reading a query with searching and paging, and evaluating the code developers type into plug-in attributes.

Quick Reference

How to Run These Examples

The examples ran in Oracle APEX 26.1 against a test application with ID 200. They need an APEX session of application 200, page 20, created with APEX_SESSION.CREATE_SESSION as shown in the guide to creating APEX sessions and managing session state from PL/SQL. Page 20 has a number item, P20_NUMBER, which one example sets. The products, categories, and orders come from the Orbit Outfitters sample schema in the orb_tables repository on GitHub.

Plug-in callbacks normally run while APEX renders a page or answers an Ajax request. To show what they write, the examples set up a web request buffer with OWA and HTP themselves, call the code, and print the buffer. The output under each example is exactly what the database printed. To create and install a plug-in from scratch, see the Oracle APEX plug-in tutorial.

The Interface: APEX_PLUGIN

Records and Results

Each callback receives the plug-in (apex_plugin.t_plugin), the component, and parameters, and returns a result record. The attributes developers set are in attributes, an apex_t_plugin_attributes object. Read them by the attribute's static ID with get_varchar2, get_number, and get_boolean. The old attribute_01 to attribute_25 fields are deprecated.

GET_INPUT_NAME_FOR_ITEM, GET_AJAX_IDENTIFIER, and GET_KEEP_BACKGROUND_EXECS

GET_INPUT_NAME_FOR_ITEM returns the name the HTML input of the item being rendered must have (p_t01 and so on), so that APEX receives its value on submit. GET_AJAX_IDENTIFIER returns the identifier the plug-in's JavaScript passes to apex.server.plugin to call the Ajax callback, as shown in the guide to apex.server and Ajax calls. Both work only while APEX renders the plug-in, and return null otherwise. GET_KEEP_BACKGROUND_EXECS returns whether background executions survive an application upgrade. GET_INPUT_NAME_FOR_PAGE_ITEM is deprecated.

The example below is the render procedure of a star rating item type. It calls the procedure the way APEX would, with the records filled in by hand, once as an editable item and once as read-only, and prints the HTML it writes.

This example needs a session of application 200, page 20.

Example:

declare
    l_item   apex_plugin.t_item;
    l_plugin apex_plugin.t_plugin;
    l_param  apex_plugin.t_item_render_param;
    l_result apex_plugin.t_item_render_result;
    l_page   htp.htbuf_arr;
    l_rows   integer := 999;
    l_name   owa.vc_arr;
    l_val    owa.vc_arr;

    -- the render procedure of an item type plug-in: a star rating as radio buttons
    procedure render_rating(p_item in apex_plugin.t_item, p_plugin in apex_plugin.t_plugin,
                            p_param in apex_plugin.t_item_render_param,
                            p_result in out nocopy apex_plugin.t_item_render_result) is
        l_max number := p_item.attributes.get_number('max_stars', p_default_value => 5);
    begin
        if p_param.is_readonly then
            apex_plugin_util.print_hidden(p_item_name => p_item.name, p_value => p_param.value);
            sys.htp.p(apex_escape.html(p_param.value) || ' of ' || l_max);
            return;
        end if;
        sys.htp.p('<div ' || apex_plugin_util.get_element_attributes(p_item, p_item.name, 'orb-rating') || '>');
        for i in 1 .. l_max loop
            sys.htp.p('<input type="radio" name="' || apex_plugin.get_input_name_for_item || '" value="' || i || '"'
                      || case when i = p_param.value then ' checked' end || '>');
        end loop;
        sys.htp.p('</div>');
        p_result.is_navigable := true;
    end;
begin
    l_name(1) := 'REQUEST_CHARSET'; l_val(1) := 'AL32UTF8';          -- a web request, as ORDS sets it up
    owa.init_cgi_env(1, l_name, l_val);
    htp.init;

    -- what APEX passes: the item's properties, the plug-in, and the value
    l_item.name       := 'P20_STAR_RATING';
    l_item.attributes := apex_t_plugin_attributes(apex_t_varchar2('max_stars', '5'));
    l_param.value     := '4';
    render_rating(l_item, l_plugin, l_param, l_result);
    l_param.is_readonly := true;
    render_rating(l_item, l_plugin, l_param, l_result);

    owa.get_page(l_page, l_rows);
    for i in 3 .. l_rows loop                                          -- skip the HTTP headers
        dbms_output.put(l_page(i));
    end loop;
    dbms_output.new_line;
end;
/

Output:

<div  id="P20_STAR_RATING" name="P20_STAR_RATING" class="orb-rating&#x20;js-ignoreChange" >
<input type="radio" name="" value="1">
<input type="radio" name="" value="2">
<input type="radio" name="" value="3">
<input type="radio" name="" value="4" checked>
<input type="radio" name="" value="5">
</div>
<input type="hidden" name="" id="P20_STAR_RATING" value="4">4 of 5

The input names are empty because the example does not run inside APEX's rendering, so GET_INPUT_NAME_FOR_ITEM returned null. GET_ELEMENT_ATTRIBUTES wrote the item's ID, name, and classes, adding js-ignoreChange to the class given. In the read-only call, PRINT_HIDDEN wrote the hidden input that carries the item's value on submit. The JavaScript side of an item plug-in is apex.item.create, covered in the guide to the apex.item JavaScript API.

Rendering: APEX_PLUGIN_UTIL

PRINT_DISPLAY_ONLY, PAGE_ITEM_NAMES_TO_JQUERY, and the first DEBUG_PAGE_ITEM signatures are deprecated. Use PRINT_READ_ONLY, ITEM_NAMES_TO_JQUERY, and DEBUG_ITEM_RENDER instead.

The next example is what the Ajax callback of an item plug-in would do to answer with a list of values. It prints only the content type and the JSON, leaving out the other HTTP headers.

This example needs a session of application 200, page 20.

Example:

declare
    l_page htp.htbuf_arr;
    l_rows integer := 999;
    l_name owa.vc_arr;
    l_val  owa.vc_arr;
begin
    l_name(1) := 'REQUEST_CHARSET'; l_val(1) := 'AL32UTF8';
    owa.init_cgi_env(1, l_name, l_val);
    htp.init;

    -- in the Ajax callback of an item plug-in: answer with the LOV as JSON
    apex_plugin_util.print_lov_as_json(
        p_sql_statement  => 'select category_name as d, category_id as r from orb_categories
                              where parent_category_id is null and category_id <= 3 order by 2',
        p_component_name => 'P20_CATEGORY',
        p_escape         => true);
    owa.get_page(l_page, l_rows);
    for i in 1 .. l_rows loop
        if l_page(i) like 'Content-Type%' or l_page(i) not like '%:%' or l_page(i) like '%"d"%' then
            dbms_output.put(l_page(i));            -- the content type and the JSON, not the other headers
        end if;
    end loop;
    dbms_output.new_line;
end;
/

Output:

Content-Type:application/json; charset=utf-8

[
{"d":"Camping","r":"1"}
,{"d":"Hiking","r":"2"}
,{"d":"Clothing","r":"3"}
]

PRINT_LOV_AS_JSON set the JSON content type and wrote each row as a d (display) and r (return) pair, with the return values as strings. Your plug-in's JavaScript receives this array directly from apex.server.plugin.

Reading Data: APEX_PLUGIN_UTIL

GET_DATA, GET_DATA2, and GET_DISPLAY_DATA

These functions run the SQL query or LOV of a plug-in attribute, with bind variables for page items, searching, and paging, and check that it has the expected number of columns (p_min_columns and p_max_columns). GET_DATA returns the values as strings, one array per column (t_column_value_list). GET_DATA2 returns typed values along with each column's name and data type (t_column_list), or runs a region's source when you pass p_region. GET_DISPLAY_DATA returns the display value for a return value, or for several with p_search_value_list, the way a Popup LOV shows it.

The search types are apex_plugin_util.c_search_contains_case, c_search_contains_ignore, c_search_exact_case, c_search_exact_ignore, c_search_like_case, c_search_like_ignore, and c_search_lookup. Always prepare the search string for its type with GET_SEARCH_STRING: the _ignore types expect it in uppercase and find nothing otherwise.

Syntax:

apex_plugin_util.get_data(p_sql_statement in varchar2, p_min_columns in number, p_max_columns in number,
    p_component_name in varchar2, p_search_type in varchar2 default null, p_search_column_name in varchar2 default null,
    p_search_string in varchar2 default null, p_first_row in number default null, p_max_rows in number default null,
    p_auto_bind_items in boolean default true, p_bind_list in t_bind_list default c_empty_bind_list)
  return t_column_value_list
apex_plugin_util.get_data2(p_sql_statement in varchar2, p_min_columns in number, p_max_columns in number,
    p_data_type_list in wwv_flow_global.vc_arr2 default c_empty_data_type_list, p_component_name in varchar2, ...)
  return t_column_list
apex_plugin_util.get_display_data(p_sql_statement in varchar2, p_min_columns in number, p_max_columns in number,
    p_component_name in varchar2, p_display_column_no in binary_integer default 1,
    p_search_column_no in binary_integer default 2, p_search_string in varchar2 | p_search_value_list in wwv_flow_global.vc_arr2,
    p_display_extra in boolean default true, ...) return varchar2 | wwv_flow_global.vc_arr2

This example needs a session of application 200, page 20.

Example:

declare
    l_lov  varchar2(400) := 'select product_name as d, product_id as r from orb_products order by 1';
    l_data apex_plugin_util.t_column_value_list;
    l_cols apex_plugin_util.t_column_list;
    l_list dbms_sql.varchar2a;
    l_find dbms_sql.varchar2a;
begin
    -- the LOV query of a plug-in: rows whose display column contains "tent", at most 3
    l_data := apex_plugin_util.get_data(
                  p_sql_statement      => l_lov,
                  p_min_columns        => 2,
                  p_max_columns        => 2,
                  p_component_name     => 'P20_PRODUCT',
                  p_search_type        => apex_plugin_util.c_search_contains_ignore,
                  p_search_column_name => 'D',
                  p_search_string      => apex_plugin_util.get_search_string(   -- prepared for the
                                              apex_plugin_util.c_search_contains_ignore, 'tent'),  -- search type
                  p_max_rows           => 3);
    for i in 1 .. l_data(1).count loop          -- one array per column
        dbms_output.put_line(l_data(2)(i) || ' = ' || l_data(1)(i));
    end loop;

    -- get_data2: typed values, and the columns' names and types
    l_cols := apex_plugin_util.get_data2(
                  p_sql_statement  => 'select sku, unit_price, launch_date from orb_products where sku like ''TNT%''',
                  p_min_columns    => 3,
                  p_max_columns    => 3,
                  p_component_name => 'P20_PRODUCT',
                  p_max_rows       => 2);
    for c in 1 .. l_cols.count loop
        dbms_output.put(rpad(l_cols(c).name || ' (' || l_cols(c).data_type || ')', 24));
    end loop;
    dbms_output.new_line;
    dbms_output.put_line(l_cols(1).value_list(1).varchar2_value || ' ' || l_cols(2).value_list(1).number_value
                         || ' ' || to_char(l_cols(3).value_list(1).date_value, 'DD-MON-YYYY'));

    -- the display value for a return value, as a Popup LOV shows it
    dbms_output.put_line('display of 2: ' || apex_plugin_util.get_display_data(
                             p_sql_statement => l_lov, p_min_columns => 2, p_max_columns => 2,
                             p_component_name => 'P20_PRODUCT', p_search_string => '2'));
    l_find(1) := '1'; l_find(2) := '3';
    l_list := apex_plugin_util.get_display_data(p_sql_statement => l_lov, p_min_columns => 2, p_max_columns => 2,
                  p_component_name => 'P20_PRODUCT', p_search_value_list => l_find);
    dbms_output.put_line('display of 1, 3: ' || l_list(1) || ', ' || l_list(2));
end;
/

Output:

3 = Basecamp 4-Person Tent
4 = Basecamp 6-Person Family Tent
5 = Ridge Ultralight Tent
SKU (VARCHAR2)          UNIT_PRICE (NUMBER)     LAUNCH_DATE (DATE)
TNT-1001 239.99 13-JUL-2022
display of 2: Trailblazer 2-Person Tent
display of 1, 3: Trailblazer 1-Person Tent, Basecamp 4-Person Tent

GET_DATA found the products whose name contains "tent" in any case and stopped at three rows, which is how a searchable plug-in pages through large lists. GET_DATA2 reported each column's name and type and returned a real number and date rather than strings. GET_DISPLAY_DATA translated return values back to product names, one at a time and as a list.

Reading Row by Row and Session State

Running Developers' Code: APEX_PLUGIN_UTIL

GET_PLSQL_EXPRESSION_RESULT, GET_PLSQL_FUNCTION_RESULT, and Their Variants

These functions evaluate a PL/SQL expression or function body that a developer entered in a plug-in attribute, with bind variables for page items (p_auto_bind_items) and others (p_bind_list). They return the result as text, as a boolean (GET_PLSQL_EXPR_RESULT_BOOLEAN and GET_PLSQL_FUNC_RESULT_BOOLEAN), or as a CLOB (the _CLOB variants). The code runs as the application's parsing schema.

REPLACE_SUBSTITUTIONS, ESCAPE, IS_EQUAL, GET_ATTRIBUTE_AS_NUMBER, and DB_OPERATION_ALLOWED

REPLACE_SUBSTITUTIONS replaces &ITEM. references in an attribute's text, escaping the values when p_escape is true. ESCAPE escapes a value for HTML only if p_escape is true, which suits an attribute that lets developers choose. IS_EQUAL compares two values and treats two nulls as equal. GET_ATTRIBUTE_AS_NUMBER converts an attribute to a number, with an error message that names the attribute. DB_OPERATION_ALLOWED checks an operation against a list such as 'UD' and returns or raises an error. IS_COMPONENT_USED evaluates a build option, an authorization, and a condition.

This example needs a session of application 200, page 20.

Example:

begin
    apex_session_state.set_value('P20_NUMBER', '12');
    -- code a developer typed into a plug-in attribute, with bind variables
    dbms_output.put_line(apex_plugin_util.get_plsql_expression_result(':P20_NUMBER * 2'));
    dbms_output.put_line(case when apex_plugin_util.get_plsql_expr_result_boolean(':P20_NUMBER > 10') then 'true' end);
    dbms_output.put_line(apex_plugin_util.get_plsql_function_result(
        'declare l_n number; begin select count(*) into l_n from orb_orders where status = ''NEW''; return l_n || '' new''; end;'));
    dbms_output.put_line(dbms_lob.getlength(apex_plugin_util.get_plsql_func_result_clob(
        'declare l_c clob; begin for i in 1 .. 20 loop l_c := l_c || rpad(''x'', 2000, ''x''); end loop; return l_c; end;'))
        || ' characters');

    dbms_output.put_line(apex_plugin_util.replace_substitutions('Hello &APP_USER., you entered &P20_NUMBER.'));
    dbms_output.put_line(apex_plugin_util.escape('<b>Tent</b>', p_escape => true));
    dbms_output.put_line(apex_plugin_util.get_html_attr('data-sku', 'TNT-1002'));
    dbms_output.put_line(apex_plugin_util.get_search_string(apex_plugin_util.c_search_contains_ignore, 'Tent'));
    dbms_output.put_line(case when apex_plugin_util.is_equal(null, null) then 'null = null' end);
    dbms_output.put_line(apex_plugin_util.get_attribute_as_number('42', 'Max. Stars'));
    begin
        dbms_output.put_line(apex_plugin_util.get_attribute_as_number('four', 'Max. Stars'));
    exception when others then
        dbms_output.put_line(regexp_replace(sqlerrm, 'ORA-\d+: '));
    end;
end;
/

Output:

24
true
2 new
40000 characters
Hello ADMIN, you entered 12
<b>Tent</b>
 data-sku="TNT-1002"
TENT
null = null
42
APEX - Entered value four for attribute "Max. Stars" is not numeric. - Contact your application administrator.

The expressions saw :P20_NUMBER as 12 through automatic binding, and the CLOB variant returned 40,000 characters, far more than a VARCHAR2 can hold. GET_SEARCH_STRING turned "Tent" into TENT for the case-insensitive search type. The last line is the error developers see when they type text into a numeric attribute, and it names the attribute, which makes it easy to fix.

EXECUTE_PLSQL_CODE and GET_POSITION_IN_LIST are deprecated. Use APEX_EXEC.EXECUTE_PLSQL, covered in the guide to APEX_EXEC, and APEX_STRING.INDEX_OF, covered in the guide to APEX_STRING.

REST Data Source Plug-ins

A REST Data Source plug-in implements a REST API style that the built-in types do not cover, with its own paging, filters, and DML. These helpers do the HTTP work using the source's settings.

The declarative side of REST Data Sources is covered in the guide to REST Data Sources in Oracle APEX.

Conclusion

APEX_PLUGIN declares the records that plug-in callbacks receive and return, plus the input names and Ajax identifiers they use. APEX_PLUGIN_UTIL prints item HTML, hidden values, options, and LOVs as JSON; reads SQL queries and LOVs with searching and paging; evaluates the PL/SQL and substitutions developers put into attributes; and does the HTTP work of REST Data Source plug-ins. Together they let a plug-in behave exactly like a built-in component.