19 APEX_PLUGIN_UTIL

The APEX_PLUGIN_UTIL package provides utility functions that solve common problems when writing a plug-in.

Topics:

DEBUG_DYNAMIC _ACTION Procedure

This procedure writes the data of the dynamic action meta data to the debug output if debugging is enabled.

Syntax

APEX_PLUGIN_UTIL.DEBUG_DYNAMIC_ACTION (
    p_plugin         IN apex_plugin.t_plugin,
    p_dynamic_action IN apex_plugin.t_dynamic_action);

Parameters

Table 19-2 describes the parameters available in the DEBUG_DYNAMIC_ACTION procedure.

Table 19-1 DEBUG_DYNAMIC_ACTION Parameters

Parameter Description

p_plugin

This is the p_plugin parameter of your plug-in function.

p_dynamic_action

This is the p_dynamic_action parameter of your plug-in function.


Example

This example shows how to collect helpful debug information during the plug-in development cycle to see what values are actually passed into the rendered function or AJAX callback function of the plug-in.

apex_plugin_util.debug_dynamic_action (
    p_plugin         => p_plugin,
    p_dynamic_action => p_dynamic_action );

DEBUG_PAGE_ITEM Procedure Signature 1

This procedure writes the data of the page item meta data to the debug output if debugging is enabled.

Syntax

APEX_PLUGIN_UTIL.DEBUG_PAGE_ITEM (
    p_plugin    IN apex_plugin.t_plugin,
    p_page_item IN apex_plugin.t_page_item);

Parameters

Table 19-2 describes the parameters available in the DEBUG_PAGE_ITEM procedure.

Table 19-2 DEBUG_PAGE_ITEM Parameters

Parameter Description

p_plugin

This is the p_plugin parameter of your plug-in function.

p_page_item

This is the p_page_item parameter of your plug-in function.


Example

This example shows how to collect helpful debug information during the plug-in development cycle to see what values are actually passed into the renderer, AJAX callback or validation function.

apex_plugin_util.debug_page_item (
    p_plugin    => p_plugin,
    p_page_item => p_page_item );

DEBUG_PAGE_ITEM Procedure Signature 2

This procedure writes the data of the page item meta data to the debug output if debugging is enabled.

Syntax

APEX_PLUGIN_UTIL.DEBUG_PAGE_ITEM (
    p_plugin              IN apex_plugin.t_plugin,
    p_page_item           IN apex_plugin.t_page_item,
    p_value               IN VARCHAR2,
    p_is_readonly         IN BOOLEAN,
    p_is_printer_friendly IN BOOLEAN);

Parameters

Table 19-3 describes the parameters available in the DEBUG_PAGE_ITEM procedure.

Table 19-3 DEBUG_PAGE_ITEM Parameters

Parameter Description

p_plugin

This is the p_plugin parameter of your plug-in function.

p_page_item

This is the p_page_item parameter of your plug-in function.

p_value

This is the p_value parameter of your plug-in function.

p_is_readonly

This is the p_is_readonly parameter of your plug-in function.

p_is_printer_friendly

This is the p_is_printer_friendly parameter of your plug-in function.


Example

This example shows how to collect helpful debug information during the plug-in development cycle to see what values are actually passed into the renderer, AJAX callback or validation function.

apex_plugin_util.debug_page_item (
    p_plugin         => p_plugin,
    p_page_item      => p_page_item, 
    p_value          => p_value,
    p_is_readonly    => p_is_readonly,
    p_is_printer_friendly => p_is_printer_friendly);

DEBUG_PROCESS Procedure

This procedure writes the data of the process meta data to the debug output if debugging is enabled.

Syntax

APEX_PLUGIN_UTIL.DEBUG_PROCESS (
    p_plugin         IN apex_plugin.t_plugin,
    p_process        IN apex_plugin.t_process);

Parameters

Table 19-4 describes the parameters available in the DEBUG_PROCESS procedure.

Table 19-4 DEBUG_PROCESS Parameters

Parameter Description

p_plugin

This is the p_plugin parameter of your plug-in function.

p_process

This is the p_process parameter of your plug-in function.


Example

This example shows how to collect helpful debug information during the plug-in development cycle to see what values are actually passed into the execution function of the plug-in.

apex_plugin_util.debug_process (
    p_plugin         => p_plugin,
    p_process        => p_process);

DEBUG_REGION Procedure Signature 1

This procedure writes the data of the region meta data to the debug output if debugging is enabled.

Syntax

APEX_PLUGIN_UTIL.DEBUG_REGION (
    p_plugin              IN apex_plugin.t_plugin,
    p_region              IN apex_plugin.t_region);

Parameters

Table 19-5 describes the parameters available in the DEBUG_REGION procedure.

Table 19-5 DEBUG_REGION Signature 1 Parameters

Parameter Description

p_plugin

This is the p_plugin parameter of your plug-in function.

p_region

This is the p_region parameter of your plug-in function.


Example

This example shows how to collect helpful debug information during the plug-in development cycle to see what values are actually passed into the render function or AJAX callback function of the plug-in.

apex_plugin_util.debug_process (
    p_plugin         => p_plugin,
    p_region        => p_region);

DEBUG_REGION Procedure Signature 2

This procedure writes the data of the region meta data to the debug output if debugging is enabled. This is the advanced version of the debugging procedure which is used for the rendering function of a region plug-in.

Syntax

APEX_PLUGIN_UTIL.DEBUG_REGION (
    p_plugin              IN apex_plugin.t_plugin,
    p_region              IN apex_plugin.t_region,
    p_is_printer_friendly IN BOOLEAN);

Parameters

Table 19-6 describes the parameters available in the DEBUG_REGION procedure.

Table 19-6 DEBUG_REGION Signature 2 Parameters

Parameter Description

p_plugin

This is the p_plugin parameter of your plug-in function

p_region

This is the p_region parameter of your plug-in function

p_is_printer_friendly

This is the p_is_printer_friendly parameter of your plug-in function


Example

This example shows how to collect helpful debug information during the plug-in development cycle to see what values are actually passed into the render function or AJAX callback function of the plug-in.

apex_plugin_util.debug_process (
    p_plugin              => p_plugin,
    p_region              => p_region,
    p_is_printer_friendly => p_is_printer_friendly);

ESCAPE Function

This function is used if you have checked the standard attribute "Has Escape Output Attribute" option for your item type plug-in which allows a developer to decide if the output should be escaped or not.

Syntax

APEX_PLUGIN_UTIL.ESCAPE (
    p_value  IN VARCHAR2,
    p_escape IN BOOLEAN)
RETURN VARCHAR2;

Parameters

Table 19-7 describes the parameters available in the ESCAPE function.

Table 19-7 ESCAPE Parameters

Parameter Description

p_value

This is the value you want to escape depending on the p_escape parameter.

p_escape

If set to TRUE, the return value is escaped. If set to FALSE, the value is not escaped.


Example

This example outputs all values of the array l_display_value_list as a HTML list and escapes the value of the array depending on the setting the developer as picked when using the plug-in.

for i in 1 .. l_display_value_list.count
loop
    sys.htp.prn (
        '<li>'||
        apex_plugin_util.escape (
            p_value  => l_display_value_list(i),
            p_escape => p_item.escape_output )||
        '</li>' );
end loop;

EXECUTE_PLSQL_CODE Procedure

This procedure executes a PL/SQL code block and performs binding of bind variables in the provided PL/SQL code. This procedure is usually used for plug-in attributes of type PL/SQL Code.

Syntax

APEX_PLUGIN_UTIL.EXECUTE_PLSQL_CODE (
    p_plsql_code  IN VARCHAR2);

Parameters

Table 19-8 describes the parameters available in the EXECUTE_PLSQL_CODE procedure.

Table 19-8 EXECUTE_PLSQL_CODE Parameters

Parameter Description

p_plsql_code

PL/SQL code to be executed.


Example

Text which should be escaped and then printed to the HTTP buffer.

declare
    l_plsql_code VARCHAR(32767) := p_process.attribute_01;
begin
    apex_plugin_util.execute_plsql_code (
        p_plsql_code => l_plsql_code );
end;

GET_DATA Function Signature 1

Executes the specified SQL query restricted by the provided search string (optional) and returns the values for each column. All column values are returned as a string, independent of their data types. The search column is identified by providing a column number in the p_search_column_no parameter.

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 2,
    p_search_column_no IN VARCHAR2 DEFAULT 2,
    p_search_string    IN VARCHAR2 DEFAULT NULL,
    p_first_row        IN NUMBER DEFAULT NULL,
    p_max_rows         IN NUMBER DEFAULT NULL)
RETURN t_column_value_list;

Parameters

Table 19-9 describes the parameters available in the GET_DATA function signature 1.

Table 19-9 GET_DATA Function Signature 1Parameters

Parameters Description

p_sql_statement

SQL statement used for the lookup.

p_min_columns

Minimum number of return columns.

p_max_columns

Maximum number of return columns.

p_component_name

In case an error is returned, this is the name of the page item or report column used to display the error message.

p_search_type

Must be one of the c_search_* constants. They are as follows: c_search_contains_case, c_search_contains_ignore, c_search_exact_case, c_search_exact_ignore

p_search_column_no

Number of the column used to restrict the SQL statement. Must be within the p_min_columns though p_max_columns range.

p_search_string

Value used to restrict the query.

p_first_row

Start query at the specified row. All rows before the specified row are skipped.

p_max_rows

Maximum number of return rows allowed.


Return

Table 19-10 describes the return value by the GET_DATA function signature 1.

Table 19-10 GET_DATA Function Signature 1 Return

Return Description

t_column_value_list

Table of apex_application_global.vc_arr2 indexed by column number.


Example

The following example shows a simple item type plug-in rendering function which executes the LOV defined for the page item and does a case sensitive LIKE filtering with the current value of the page item. The result is then generated as a HTML list.

function render_list (
    p_item                in apex_plugin.t_page_item,
    p_value               in varchar2,
    p_is_readonly         in boolean,
    p_is_printer_friendly in boolean )
    return apex_plugin.t_page_item_render_result
is
    l_column_value_list   apex_plugin_util.t_column_value_list;
begin
    l_column_value_list :=
        apex_plugin_util.get_data (
            p_sql_statement    => p_item.lov_definition,
            p_min_columns      => 2,
            p_max_columns      => 2,
            p_component_name   => p_item.name,
            p_search_type      => apex_plugin_util.c_search_contains_case,
            p_search_column_no => 1,
            p_search_string    => p_value );
 
    sys.htp.p('<ul>');
    for i in 1 .. l_column_value_list(1).count
    loop
        sys.htp.p(
            '<li>'||
            sys.htf.escape_sc(l_column_value_list(1)(i))|| -- display column
            '-'||
            sys.htf.escape_sc(l_column_value_list(2)(i))|| -- return column
            '</li>');
    end loop;
    sys.htp.p('</ul>');
end render_list;

GET_DATA Function Signature 2

Executes the specified SQL query restricted by the provided search string (optional) and returns the values for each column. All column values are returned as a string, independent of their data types. The search column is identified by providing a column name in the p_search_column_name parameter.

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)
RETURN t_column_value_list;

Parameters

Table 19-11 describes the parameters available for GET_DATA function signature 2.

Table 19-11 GET_DATA Function Signature 2 Parameters

Parameters Description

p_sql_statement

SQL statement used for the lookup.

p_min_columns

Minimum number of return columns.

p_max_columns

Maximum number of return columns.

p_component_name

In case an error is returned, this is the name of the page item or report column used to display the error message.

p_search_type

Must be one of the c_search_* constants. They are as follows: c_search_contains_case, c_search_contains_ignore, c_search_exact_case, c_search_exact_ignore

p_search_column_name

This is the column name used to restrict the SQL statement.

p_search_string

Value used to restrict the query.

p_first_row

Start query at the specified row. All rows before the specified row are skipped.

p_max_rows

Maximum number of return rows allowed.


Return

Table 19-12 describes the return value by the GET_DATA function signature 2.

Table 19-12 GET_TABLE Function Signature 2

Parameter Description

t_column_value_list

Table of apex_application_global.vc_arr2 indexed by column number.


Example

The following example shows a simple item type plug-in rendering function which executes the LOV defined for the page item and does a case sensitive LIKE filtering with the current value of the page item. The result is then generated as a HTML list.

function render_list (
    p_item                in apex_plugin.t_page_item,
    p_value               in varchar2,
    p_is_readonly         in boolean,
    p_is_printer_friendly in boolean )
    return apex_plugin.t_page_item_render_result
is
    l_column_value_list   apex_plugin_util.t_column_value_list;
begin
    l_column_value_list :=
        apex_plugin_util.get_data (
            p_sql_statement    => p_item.lov_definition,
            p_min_columns      => 2,
            p_max_columns      => 2,
            p_component_name   => p_item.name,
            p_search_type      => apex_plugin_util.c_search_contains_case,
            p_search_column_name => 'ENAME',
            p_search_string    => p_value );
 
    sys.htp.p('<ul>');
    for i in 1 .. l_column_value_list(1).count
    loop
        sys.htp.p(
            '<li>'||
            sys.htf.escape_sc(l_column_value_list(1)(i))|| -- display column
            '-'||
            sys.htf.escape_sc(l_column_value_list(2)(i))|| -- return column
            '</li>');
    end loop;
    sys.htp.p('</ul>');
end render_list;

GET_DATA2 Function Signature 1

Executes the specified SQL query restricted by the provided search string (optional) and returns the values for each column. All column values are returned along with their original data types. The search column is identified by providing a column number in the p_search_column_no parameter.

Syntax

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_GLOBAL.VC_ARR2 DEFAULT C_EMPTY_DATA_TYPE_LIST,
    p_component_name   IN VARCHAR2,
    p_search_type      IN VARCHAR2 DEFAULT 2,
    p_search_column_no IN VARCHAR2 DEFAULT 2,
    p_search_string    IN VARCHAR2 DEFAULT NULL,
    p_first_row        IN NUMBER DEFAULT NULL,
    p_max_rows         IN NUMBER DEFAULT NULL)
RETURN t_column_value_list2;

Parameters

Table 19-13 describes the parameters available in the GET_DATA2 function.

Table 19-13 GET_DATA2 Parameters

Parameter Description

p_sql_statement

SQL statement used for the lookup.

p_min_columns

Minimum number of return columns.

p_max_columns

Maximum number of return columns.

p_data_type_list

If provided, checks to make sure the data type for each column matches the specified data type in the array. Use the constants c_data_type_* for available data types.

p_component_name

In case an error is returned, this is the name of the page item or report column used to display the error message.

p_search_type

Must be one of the c_search_* constants. They are as follows: c_search_contains_case, c_search_contains_ignore, c_search_exact_case, c_search_exact_ignore

p_search_column_no

Number of the column used to restrict the SQL statement. Must be within the p_min_columns though p_max_columns range.

p_search_string

Value used to restrict the query.

p_first_row

Start query at the specified row. All rows before the specified row are skipped.

p_max_rows

Maximum number of return rows allowed.


Return

Table 19-14 describes the return value by the GET_DATA2 function.

Table 19-14 GET_DATA2 Return

Return Description

t_column_value_list2

Table of t_column_values indexed by column number.


Example

The following example is a simple item type plug-in rendering function which executes the LOV defined for the page item and does a case sensitive LIKE filtering with the current value of the page item. The result is then generated as a HTML list. This time, the first column of the LOV SQL statement is checked if it is of type VARCHAR2 and the second is of type number.

function render_list (
    p_item                in apex_plugin.t_page_item,
    p_value               in varchar2,
    p_is_readonly         in boolean,
    p_is_printer_friendly in boolean )
    return apex_plugin.t_page_item_render_result
is
    l_data_type_list    apex_application_global.vc_arr2;
    l_column_value_list apex_plugin_util.t_column_value_list2;
begin
    -- The first LOV column has to be a string and the second a number
    l_data_type_list(1) := apex_plugin_util.c_data_type_varchar2;
    l_data_type_list(2) := apex_plugin_util.c_data_type_number;
    --
    l_column_value_list :=
        apex_plugin_util.get_data2 (
            p_sql_statement    => p_item.lov_definition,
            p_min_columns      => 2,
            p_max_columns      => 2,
            p_data_type_list   => l_data_type_list,
            p_component_name   => p_item.name,
            p_search_type      => apex_plugin_util.c_search_contains_case,
            p_search_column_no => 1,
            p_search_string    => p_value );
    --
    sys.htp.p('<ul>');
    for i in 1 .. l_column_value_list.count(1)
    loop
        sys.htp.p(
            '<li>'||
            sys.htf.escape_sc(l_column_value_list(1).value_list(i).varchar2_value)|| -- display column
            '-'||
            sys.htf.escape_sc(l_column_value_list(2).value_list(i).number_value)|| -- return column
            '</li>');
    end loop;
    sys.htp.p('</ul>');
end render_list;

GET_DATA2 Function Signature 2

Executes the specified SQL query restricted by the provided search string (optional) and returns the values for each column. All column values are returned along with their original data types. The search column is identified by providing a column number in the p_search_column_no parameter.

Syntax

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_GLOBAL.VC_ARR2 DEFAULT C_EMPTY_DATA_TYPE_LIST,
    p_component_name     IN VARCHAR2,
    p_search_type        IN VARCHAR2 DEFAULT 2,
    p_search_column_name IN VARCHAR2 DEFAULT 2,
    p_search_string      IN VARCHAR2 DEFAULT NULL,
    p_first_row          IN NUMBER DEFAULT NULL,
    p_max_rows           IN NUMBER DEFAULT NULL)
RETURN t_column_value_list2;

Parameters

Table 19-15 describes the parameters available in the GET_DATA2 function signature 2.

Table 19-15 GET_DATA2 Function Signature 2

Parameter Description

p_sql_statement

SQL statement used for the lookup.

p_min_columns

Minimum number of return columns.

p_max_columns

Maximum number of return columns.

p_data_type_list

If provided, checks to make sure the data type for each column matches the specified data type in the array. Use the constants c_data_type_* for available data types.

p_component_name

In case an error is returned, this is the name of the page item or report column used to display the error message.

p_search_type

Must be one of the c_search_* constants. They are as follows: c_search_contains_case, c_search_contains_ignore, c_search_exact_case, c_search_exact_ignore

p_search_column_name

The column name used to restrict the SQL statement.

p_search_string

Value used to restrict the query.

p_first_row

Start query at the specified row. All rows before the specified row are skipped.

p_max_rows

Maximum number of return rows allowed.


Return

Table 19-16 describes the return value by the GET_DATA2 function signature 2.

Table 19-16 GET_DATA2 Function Signature 2 Return

Parameter Description

t_column_value_list2

Table of t_column_values indexed by column number.


Example

The following example is a simple item type plug-in rendering function which executes the LOV defined for the page item and does a case sensitive LIKE filtering with the current value of the page item. The result is then generated as a HTML list. This time, the first column of the LOV SQL statement is checked if it is of type VARCHAR2 and the second is of type number.

function render_list (
    p_item                in apex_plugin.t_page_item,
    p_value               in varchar2,
    p_is_readonly         in boolean,
    p_is_printer_friendly in boolean )
    return apex_plugin.t_page_item_render_result
is
    l_data_type_list    apex_application_global.vc_arr2;
    l_column_value_list apex_plugin_util.t_column_value_list2;
begin
    -- The first LOV column has to be a string and the second a number
    l_data_type_list(1) := apex_plugin_util.c_data_type_varchar2;
    l_data_type_list(2) := apex_plugin_util.c_data_type_number;
    --
    l_column_value_list :=
        apex_plugin_util.get_data2 (
            p_sql_statement    => p_item.lov_definition,
            p_min_columns      => 2,
            p_max_columns      => 2,
            p_data_type_list   => l_data_type_list,
            p_component_name   => p_item.name,
            p_search_type      => apex_plugin_util.c_search_contains_case,
            p_search_column_name => 'ENAME',
            p_search_string    => p_value );
    --
    sys.htp.p('<ul>');
    for i in 1 .. l_column_value_list.count(1)
    loop
        sys.htp.p(
            '<li>'||
            sys.htf.escape_sc(l_column_value_list(1).value_list(i).varchar2_value)|| -- display column
            '-'||
            sys.htf.escape_sc(l_column_value_list(2).value_list(i).number_value)|| -- return column
            '</li>');
    end loop;
    sys.htp.p('</ul>');
end render_list;

GET_DISPLAY_DATA Function Signature 1

This function gets the display lookup value for the value specified in p_search_string.

Syntax

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 DEFAULT NULL,
    p_display_extra    IN BOOLEAN DEFAULT TRUE)
RETURN VARCHAR2;

Parameters

Table 19-17 describes the parameters available in the GET_DISPLAY_DATA function signature 1.

Table 19-17 GET_DISPLAY_DATA Signature 1 Parameters

Parameter Description

p_sql_statement

SQL statement used for the lookup.

p_min_columns

Minimum number of return columns.

p_max_columns

Maximum number of return columns.

p_component_name

In case an error is returned, this is the name of the page item or report column used to display the error message.

p_display_column_no

Number of the column returned from the SQL statement. Must be within the p_min_columns though p_max_columns range

p_search_column_no

Number of the column used to restrict the SQL statement. Must be within the p_min_columns though p_max_columns range.

p_search_string

Value used to restrict the query.

p_display_extra

If set to TRUE, and a value is not found, the search value is added to the result instead.


Return

Table 19-18 describes the return value by the GET_DISPLAY_DATA function signature 1.

Table 19-18 GET_DISPLAY_DATA Signature 1 Return

Return Description

VARCHAR2

Value of the first record of the column specified by p_display_column_no. If no record was found it contains the value of p_search_string if the parameter p_display_extra is set to TRUE. Otherwise NULL is returned.


Example

The following example does a lookup with the value provided in p_value and returns the display column of the LOV query.

function render_value (
    p_item                in apex_plugin.t_page_item,
    p_value               in varchar2,
    p_is_readonly         in boolean,
    p_is_printer_friendly in boolean )
    return apex_plugin.t_page_item_render_result
is
begin
    sys.htp.p(sys.htf.escape_sc(
        apex_plugin_util.get_display_data (
            p_sql_statement     => p_item.lov_definition,
            p_min_columns       => 2,
            p_max_columns       => 2,
            p_component_name    => p_item.name,
            p_display_column_no => 1,
            p_search_column_no  => 2,
            p_search_string     => p_value )));
end render_value;

GET_DISPLAY_DATA Function Signature 2

This function looks up all the values provided in the p_search_value_list instead of just a single value lookup.

Syntax

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_value_list IN ww_flow_global.vc_arr2,
    p_display_extra     IN BOOLEAN DEFAULT TRUE)
RETURN apex_application_global.vc_arr2;

Parameters

Table 19-19 describes the parameters available in the GET_DISPLAY_DATA function signature 2.

Table 19-19 GET_DISPLAY_DATA Signature 2 Parameters

Parameter Description

p_sql_statement

SQL statement used for the lookup.

p_min_columns

Minimum number of return columns.

p_max_columns

Maximum number of return columns.

p_component_name

In case an error is returned, this is the name of the page item or report column used to display the error message.

p_display_column_no

Number of the column returned from the SQL statement. Must be within the p_min_columns though p_max_columns range.

p_search_column_no

Number of the column used to restrict the SQL statement. Must be within the p_min_columns though p_max_columns range.

p_search_value_list

Array of values to look up.

p_display_extra

If set to TRUE, and a value is not found, the search value is added to the result instead.


Return

Table 19-20 describes the return value by the GET_DISPLAY_DATA function signature 2.

Table 19-20 GET_DISPLAY_DATA Signature 2 Return

Return Description

apex_application_global.vc_arr2

List of VARCHAR2 indexed by pls_integer. For each entry in p_search_value_list the resulting array contains the value of the first record of the column specified by p_display_column_no in the same order as in p_search_value_list. If no record is found it contains the value of p_search_string if the parameter p_display_extra is set to TRUE. Otherwise the value is skipped.


Example

Looks up the values 7863, 7911 and 7988 and generates a HTML list with the value of the corresponding display column in the LOV query.

function render_list (
    p_plugin              in apex_plugin.t_plugin,
    p_item                in apex_plugin.t_page_item,
    p_value               in varchar2,
    p_is_readonly         in boolean,
    p_is_printer_friendly in boolean )
    return apex_plugin.t_page_item_render_result
is
    l_search_list apex_application_global.vc_arr2;
    l_result_list apex_application_global.vc_arr2;
begin
    l_search_list(1) := '7863';
    l_search_list(2) := '7911';
    l_search_list(3) := '7988';
    --
    l_result_list :=
        apex_plugin_util.get_display_data (
            p_sql_statement     => p_item.lov_definition,
            p_min_columns       => 2,
            p_max_columns       => 2,
            p_component_name    => p_item.name,
            p_search_column_no  => 1,
            p_search_value_list => l_search_list );
    --
    sys.htp.p('<ul>');
    for i in 1 .. l_result_list.count
    loop
        sys.htp.p(
            '<li>'||
            sys.htf.escape_sc(l_result_list(i))||
            '</li>');
    end loop;
    sys.htp.p('</ul>');
end render_list;

GET_ELEMENT_ATTRIBUTES Function

This function returns some of the standard attributes of an HTML element (for example, id, name, required, placeholder, aria-error-attributes, class) which is used if a HTML input/select/textarea/... tag is generated to get a consistent set of attributes.

Syntax

APEX_PLUGIN_UTIL.GET_ELEMENT_ATTRIBUTES (
    p_item           IN APEX_PLUGIN.T_PAGE_ITEM,
    p_name           IN VARCHAR2 DEFAULT NULL,
    p_default_class  IN VARCHAR2 DEFAULT NULL,
    p_add_id         in boolean  default true )
    return varchar2;

Parameters

Table 19-21 describes the available parameters for GET_ELEMENT_ATTRIBUTES function.

Table 19-21 GET_ELEMENT_ATTRIBUTES Function Parameters

Parameters Description

p_item

This is the p_item parameter of your plug-in function.

p_name

This is the value which has been return by apex_plugin.get_input_name_or_page_item

p_default_class

Default CSS class which which should be contained in the result string.

p_add_id

If set to TRUE then the id attribute is also contained in the result string.


Example

This example emits an INPUT tag of type text which uses apex_plugin_util.get_element_attributes to automatically include the most common attributes.

sys.htp.prn (
        '<input type="text" ' ||
        apex_plugin_util.get_element_attributes(p_item, l_name, 'text_field') ||
        'value="'||l_escaped_value||'" '||
        'size="'||p_item.element_width||'" '||
        'maxlength="'||p_item.element_max_length||'" '||
        ' />');

GET_PLSQL_EXPRESSION_RESULT Function

This function executes a PL/SQL expression and returns a result. This function also performs the binding of any bind variables in the provided PL/SQL expression. This function is usually used for plug-in attributes of type PL/SQL Expression.

Syntax

APEX_PLUGIN_UTIL.GET_PLSQL_EXPRESSION_RESULT (
    p_plsql_expression IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 19-22 describes the parameters available in the GET_PLSQL_EXPRESSION_RESULT function.

Table 19-22 GET_PLSQL_EXPRESSION_RESULT Parameters

Parameter Description

p_plsql_expression_result

A PL/SQL expression that returns a string.


Return

Table 19-23 describes the return value by the function GET_PLSQL_EXPRESSION_RESULT.

Table 19-23 GET_PLSQL_EXPRESSION_RESULT Return

Return Description

VARCHAR2

String result value returned by the PL/SQL Expression.


Example

This example executes and returns the result of the PL/SQL expression which is specified in attribute_03 of an item type plug-in attribute of type "PL/SQL Expression".

l_result := apex_plugin_util.get_plsql_expression_result (
    p_plsql_expression => p_item.attribute_03 );

GET_PLSQL_FUNCTION_RESULT Function

This function executes a PL/SQL function block and returns the result. This function also performs binding of bind variables in the provided PL/SQL Function Body. This function is usually used for plug-in attributes of type PL/SQL Function Body.

Syntax

APEX_PLUGIN_UTIL.GET_PLSQL_FUNCTION_RESULT (
    p_plsql_function IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 19-24 describes the parameters available in the GET_PLSQL_FUNCTION_RESULT function.

Table 19-24 GET_PLSQL_FUNCTION_RESULT Parameters

Parameter Description

p_plsql_function

A PL/SQL function block that returns a result of type string.


Return

Table 19-25 describes the return value by the function GET_PLSQL_FUNCTION_RESULT.

Table 19-25 GET_PLSQL_FUNCTION_RESULT Return

Return Description

VARCHAR2

String result value returned by the PL/SQL function block.


Example

The following example executes and returns the result of the PL/SQL function body that is specified in attribute_03 of an item type plug-in attribute of type PL/SQL Function Body.

l_result := apex_plugin_util.get_plsql_function_result (
    p_plsql_function => p_item.attribute_03 );

GET_POSITION_IN_LIST Function

This function returns the position in the list where p_value is stored. If it is not found, null is returned.

Syntax

APEX_PLUGIN_UTIL.GET_POSITION_IN_LIST(
    p_list IN apex_application_global.vc_arr2,
    p_value IN VARCHAR2)
RETURN NUMBER;

Parameters

Table 19-26 describes the parameters available in the GET_POSITION_IN_LIST function.

Table 19-26 GET_POSITION_IN_LIST Parameters

Parameter Description

p_list

Array of type apex_application_global.vc_arr2 that contains entries of type VARCHAR2.

p_value

Value located in the p_list array.


Return

Table 19-27 describes the return value by the GET_POISTION_IN_LIST function.

Table 19-27 GET_POSITION_IN_LIST Return

Return Description

NUMBER

Returns the position of p_value in the array p_list. If it is not found NULL is returned.


Example

The following example searchs for "New York" in the provided list and returns 2 into l_position.

declare
    l_list     apex_application_global.vc_arr2;
    l_position number;
begin
    l_list(1) := 'Rome';
    l_list(2) := 'New York';
    l_list(3) := 'Vienna';
    
    l_position := apex_plugin_util.get_position_in_list (
                      p_list  => l_list,
                      p_value => 'New York' );
end;

GET_SEARCH_STRING Function

Based on the provided value in p_search_type the passed in value of p_search_string is returned unchanged or is converted to uppercase. Use this function with the p_search_string parameter of get_data and get_data2.

Syntax

APEX_PLUGIN_UTIL.GET_SEARCH_STRING(
    p_search_type IN VARCHAR2,
    p_search_string IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 19-28 describes the parameters available in the GET_SEARCH_STRING function.

Table 19-28 GET_SEARCH_STRING Parameters

Parameter Description

p_search_type

Type of search when used with get_data and get_data2. Use one of the c_search_* constants.

p_search_string

Search string used for the search with get_data and get_data2.


Return

Table 19-29 describes the return value by the function GET_SEARCH_STRING.

Table 19-29 GET_SEARCH_STRING Return

Return Description

VARCHAR2

Returns p_search_string unchanged or in uppercase if p_search_type is of type c_search_contains_ignore or c_search_exact_ignore.


Example

This example uses a call to get_data or get_data2 to make sure the search string is using the correct case.

l_column_value_list :=
    apex_plugin_util.get_data (
        p_sql_statement    => p_item.lov_definition,
        p_min_columns      => 2,
        p_max_columns      => 2,
        p_component_name   => p_item.name,
        p_search_type      => apex_plugin_util.c_search_contains_ignore,
        p_search_column_no => 1,
        p_search_string    => apex_plugin_util.get_search_string (
            p_search_type   => apex_plugin_util.c_search_contains_ignore,
            p_search_string => p_value ) );

IS_EQUAL Function

This function returns TRUE if both values are equal and FALSE if not. If both values are NULL, TRUE is returned.

Syntax

APEX_PLUGIN_UTIL.IS_EQUAL (
    p_value1 IN VARCHAR2
    p_value2 IN VARCHAR2)
RETURN BOOLEAN;

Parameters

Table 19-30 describes the parameters available in the IS_EQUAL function.

Table 19-30 IS_EQUAL Parameters

Parameter Description

p_value1

First value to compare.

p_value2

Second value to compare.


Return

Table 19-31 describes the return value by the function IS_EQUAL.

Table 19-31 IS_EQUAL Return

Return Description

BOOLEAN

Returns TRUE if both values are equal or both values are NULL, otherwise it returns FALSE.


Example

In the following example, if the value in the database is different from what is entered, the code in the if statement is executed.

if NOT apex_plugin_util.is_equal(l_database_value, l_current_value) then
    -- value has changed, do something
    null;
end if;

PAGE_ITEM_NAMES_TO_JQUERY Function

This function returns a jQuery selector based on a comma delimited string of page item names. For example, you could use this function for a plug-in attribute called "Page Items to Submit" where the JavaScript code has to read the values of the specified page items.

Syntax

APEX_PLUGIN_UTIL.PAGE_ITEM_NAMES_TO_JQUERY (
    p_page_item_names IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Table 19-32 describes the parameters available in the PAGE_ITEM_NAMES_TO_JQUERY function.

Table 19-32 PAGE_ITEM_NAMES_TO_JQUERY Parameters

Parameter Description

p_page_item_names

Comma delimited list of page item names.


Return

Table 19-31 describes the return value by the PAGE_ITEM_NAMES_TO_JQUERY function.

Table 19-33 PAGE_ITEM_NAMES_TO_JQUERY Return

Return Description

VARCHAR2

Transforms the page items specified in p_page_item_names into a jQuery selector.


Example

The following example shows the code to construct the initialization call for a JavaScript function called myOwnWidget. This function gets an object with several attributes where one attribute is pageItemsToSubmit which is expected to be a jQuery selector.

apex_javascript.add_onload_code (
    p_code => 'myOwnWidget('||
                  '"#'||p_item.name||'",'||
                  '{'||
                  apex_javascript.add_attribute('ajaxIdentifier',      apex_plugin.get_ajax_identifier)||
                  apex_javascript.add_attribute('dependingOnSelector', apex_plugin_util.page_item_names_to_jquery(p_item.lov_cascade_parent_items))||
                  apex_javascript.add_attribute('optimizeRefresh',     p_item.ajax_optimize_refresh)||
                  apex_javascript.add_attribute('pageItemsToSubmit',   apex_plugin_util.page_item_names_to_jquery(p_item.ajax_items_to_submit))||
                  apex_javascript.add_attribute('nullValue',           p_item.lov_null_value, false, false)||
                  '});' );

PRINT_DISPLAY_ONLY Procedure

This procedure outputs a SPAN tag for a display only field.

Syntax

APEX_PLUGIN_UTIL.PRINT_DISPLAY_ONLY (
    p_item_name        IN VARCHAR2,
    p_display_value    IN VARCHAR2,
    p_show_line_breaks IN BOOLEAN,
    p_attributes       IN VARCHAR2,
    p_id_postfix       IN VARCHAR2 DEFAULT '_DISPLAY');

Parameters

Table 19-34 describes the parameters available in the PRINT_DISPLAY_ONLY procedure.

Table 19-34 PRINT_DISPLAY_ONLY Parameter

Parameter Description

p_item_name

Name of the page item. This parameter should be called with p_item.name.

p_display_value

Text to be displayed.

p_show_line_breaks

If set to TRUE line breaks in p_display_value are changed to <br /> so that the browser renders them as line breaks.

p_attributes

Additional attributes added to the SPAN tag.

p_id_postfix

Postfix which is getting added to the value in p_item_name to get the ID for the SPAN tag. Default is _DISPLAY.


Example

The following code could be used in an item type plug-in to render a display only page item.

apex_plugin_util.print_display_only (
    p_item_name        => p_item.name,
    p_display_value    => p_value,
    p_show_line_breaks => false,
    p_escape           => true,
    p_attributes       => p_item.element_attributes );

PRINT_ESCAPED_VALUE Procedure

This procedure outputs the value in an escaped form and chunks big strings into smaller outputs.

Syntax

APEX_PLUGIN_UTIL.PRINT_ESCAPED_VALUE (
    p_value   IN VARCHAR2);

Parameters

Table 19-35 describes the parameters available in the PRINT_ESCAPED_VALUE procedure.

Table 19-35 PRINT_ESCAPED_VALUE Parameter

Parameter Description

p_value

Text which should be escaped and then printed to the HTTP buffer.


Example

Prints a hidden field with the current value of the page item.

sys.htp.prn('<input type="hidden" name="'" id="'||p_item_name||'" value="');
print_escaped_value(p_value);
sys.htp.prn('">');

PRINT_HIDDEN_IF_READONLY Procedure

This procedure outputs a hidden field to store the page item value if the page item is rendered as readonly and is not printer friendly. If this procedure is called in an item type plug-in, the parameters of the plug-in interface should directly be passed in.

Syntax

APEX_PLUGIN_UTIL.PRINT_HIDDEN_IF_READ_ONLY (
    p_item_name   IN VARCHAR2,
    p_value       IN VARCHAR2,
    p_is_readonly IN BOOLEAN,
    p_is_printer_friendly IN BOOLEAN,
    p_id_postfix  IN VARCHAR2 DEFAULT NULL);

Parameters

Table 19-36 describes the parameters available in the PRINT_HIDDEN_IF_READONLY procedure.

Table 19-36 PRINT_HIDDEN_IF_READONLY Parameters

Parameter Description

p_item_name

Name of the page item. For this parameter the p_item.name should be passed in.

p_value

Current value of the page item. For this parameter p_value should be passed in.

p_is_readonly

Is the item rendered readonly. For this parameter p_is_readonly should be passed in.

p_is_printer_friendly

Is the item rendered in printer friendly mode. For this parameter p_is_printer_friendly should be passed in.

p_id_postfix

Used to generate the ID attribute of the hidden field. It is build based on p_item_name and the value in p_id_postfix.


Example

Writes a hidden field with the current value to the HTTP output if p_is_readonly is TRUE and p_printer_friendly is FALSE.

apex_plugin_util.print_hidden_if_readonly (
    p_item_name           => p_item.name,
    p_value               => p_value,
    p_is_readonly         => p_is_readonly,
    p_is_printer_friendly => p_is_printer_friendly );

PRINT_JSON_HTTP_HEADER Procedure

This procedure outputs a standard HTTP header for a JSON output.

Syntax

APEX_PLUGIN_UTIL.PRINT_JSON_HTTP_HEADER;

Parameters

None.

Example

This example shows how to use this procedure in the AJAX callback function of a plugin. This code outputs a JSON structure in the following format: [{"d":"Display 1","r":"Return 1"},{"d":"Display 2","r":"Return 2"}]

-- Write header for the JSON stream.
apex_plugin_util.print_json_http_header;
-- initialize the JSON structure
sys.htp.p('[');
-- loop through the value array
for i in 1 .. l_values.count
loop
    -- add array entry
    sys.htp.p (
        case when i > 1 then ',' end||
        '{'||
        apex_javascript.add_attribute('d', sys.htf.escape_sc(l_values(i).display_value), false, true)||
        apex_javascript.add_attribute('r', sys.htf.escape_sc(l_values(i).return_value), false, false)||
        '}' );
end loop;
-- close the JSON structure
sys.htp.p(']');

PRINT_LOV_AS_JSON Procedure

This procedure outputs a JSON response based on the result of a two column LOV in the format:

[{"d:"display","r":"return"},{"d":....,"r":....},....]

Note:

The HTTP header is initialized with MIME type "application/json" as well.

Syntax

APEX_PLUGIN_UTIL.PRINT_LOV_AS_JSON (
    p_sql_statement         IN VARCHAR2,
    p_component_name        IN VARCHAR2,
    p_escape                IN BOOLEAN,
    p_replace_substitutions IN BOOLEAN DEFAULT FALSE);

Parameters

Table 19-37 describes the parameters available in the PRINT_LOV_AS_JSON procedure.

Table 19-37 PRINT_LOV_AS_JSON Parameters

Parameter Description

p_sql_statement

A SQL statement which returns two columns from the SELECT.

p_component_name

The name of the page item or report column that is used in case an error is displayed.

p_escape

If set to TRUE the value of the display column is escaped, otherwise it is output as is.

p_replace_substitutions

If set to TRUE, apex_plugin_util.replace_substitutions is called for the value of the display column, otherwise, it is output as is.


Example

This example shows how to use the procedure in an AJAX callback function of an item type plug-in. The following call writes the LOV result as a JSON array to the HTTP output.

apex_plugin_util.print_lov_as_json (
    p_sql_statement  => p_item.lov_definition,
    p_component_name => p_item.name,
    p_escape         => true );

PRINT_OPTION Procedure

This procedure outputs an OPTION tag.

Syntax

APEX_PLUGIN_UTIL.PRINT_OPTION (
    p_display_value       IN VARCHAR2,
    p_return_value        IN VARCHAR2,
    p_is_selected         IN BOOLEAN,
    p_attributes          IN VARCHAR2,
    p_escape              IN BOOLEAN DEFAULT TRUE);

Parameters

Table 19-38 describes the parameters available in the PRINT_OPTION procedure.

Table 19-38 PRINT_OPTION Parameters

Parameter Description

p_display_value

Text which is displayed by the option.

p_return_value

Value which is set when the option is picked.

p_is_selected

Set to TRUE if the selected attribute should be set for this option.

p_attributes

Additional HTML attributes which should be set for the OPTION tag.

p_escape

Set to TRUE if special characters in p_display_value should be escaped.


Example

The following example could be used in an item type plug-in to create a SELECT list. Use apex_plugin_util.is_equal to find out which list entry should be marked as current.

sys.htp.p('<select id="'||p_item.name||'" size="'||nvl(p_item.element_height, 5)||'" '||coalesce(p_item.element_attributes, 'class="new_select_list"')||'>');
-- loop through the result and add list entries
for i in 1 .. l_values.count
loop
    apex_plugin_util.print_option (
        p_display_value => l_values(i).display_value,
        p_return_value  => l_values(i).return_value,
        p_is_selected   => apex_plugin_util.is_equal(l_values(i).return_value, p_value),
        p_attributes    => p_item.element_option_attributes,
        p_escape        => true );
end loop;
sys.htp.p('</select>');

REPLACE_SUBSTITUTIONS Function

This function replaces any &ITEM. substitution references with their actual value. If p_escape is set to TRUE, any special characters contained in the value of the referenced item are escaped to prevent Cross-site scripting (XSS) attacks.

Syntax

apex_plugin_util.replace_substitutions (
    p_value    in varchar2,
    p_escape   in boolean default true )
    return varchar2;

Parameters

Table 19-39 describes the parameters available in the REPLACE_SUBSTITUTION function.

Table 19-39 REPLACE_SUBSTITUTION Parameters

Parameter Description

p_value

This value is a string which can contain several &ITEM. references which are replaced by their actual page item values.

p_escape

If set to TRUE any special characters contained in the value of the referenced item are escaped to prevent Cross-site scripting (XSS) attacks. If set to FALSE, the referenced items are not escaped.


Example

The following example replaces any substitution syntax references in the region plug-in attribute 05 with their actual values. Any special characters in the values are escaped.

l_advanced_formatting  := apex_plugin_util.replace_substitutions (
                              p_value => p_region.attribute_05,
                              p_escape => true );