The APEX_PLUGIN_UTIL package provides utility functions that solve common problems when writing a plug-in.
This procedure writes the data of the dynamic action meta data to the debug output if debugging is enabled.
APEX_PLUGIN_UTIL.DEBUG_DYNAMIC_ACTION (
p_plugin IN apex_plugin.t_plugin,
p_dynamic_action IN apex_plugin.t_dynamic_action);
Table 19-2 describes the parameters available in the DEBUG_DYNAMIC_ACTION procedure.
Table 19-1 DEBUG_DYNAMIC_ACTION Parameters
| Parameter | Description |
|---|---|
|
|
This is the |
|
|
This is the |
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 );
This procedure writes the data of the page item meta data to the debug output if debugging is enabled.
APEX_PLUGIN_UTIL.DEBUG_PAGE_ITEM (
p_plugin IN apex_plugin.t_plugin,
p_page_item IN apex_plugin.t_page_item);
Table 19-2 describes the parameters available in the DEBUG_PAGE_ITEM procedure.
Table 19-2 DEBUG_PAGE_ITEM Parameters
| Parameter | Description |
|---|---|
|
|
This is the |
|
p_page_item |
This is the |
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 );
This procedure writes the data of the page item meta data to the debug output if debugging is enabled.
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);
Table 19-3 describes the parameters available in the DEBUG_PAGE_ITEM procedure.
Table 19-3 DEBUG_PAGE_ITEM Parameters
| Parameter | Description |
|---|---|
|
|
This is the |
|
|
This is the |
|
|
This is the |
|
|
This is the |
|
|
This is the |
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);
This procedure writes the data of the process meta data to the debug output if debugging is enabled.
APEX_PLUGIN_UTIL.DEBUG_PROCESS (
p_plugin IN apex_plugin.t_plugin,
p_process IN apex_plugin.t_process);
Table 19-4 describes the parameters available in the DEBUG_PROCESS procedure.
Table 19-4 DEBUG_PROCESS Parameters
| Parameter | Description |
|---|---|
|
|
This is the |
|
|
This is the |
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);
This procedure writes the data of the region meta data to the debug output if debugging is enabled.
APEX_PLUGIN_UTIL.DEBUG_REGION (
p_plugin IN apex_plugin.t_plugin,
p_region IN apex_plugin.t_region);
Table 19-5 describes the parameters available in the DEBUG_REGION procedure.
Table 19-5 DEBUG_REGION Signature 1 Parameters
| Parameter | Description |
|---|---|
|
|
This is the |
|
|
This is the |
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);
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.
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);
Table 19-6 describes the parameters available in the DEBUG_REGION procedure.
Table 19-6 DEBUG_REGION Signature 2 Parameters
| Parameter | Description |
|---|---|
|
|
This is the |
|
|
This is the |
|
|
This is the |
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);
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.
APEX_PLUGIN_UTIL.ESCAPE (
p_value IN VARCHAR2,
p_escape IN BOOLEAN)
RETURN VARCHAR2;
Table 19-7 describes the parameters available in the ESCAPE function.
| Parameter | Description |
|---|---|
|
|
This is the value you want to escape depending on the |
|
|
If set to |
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;
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.
APEX_PLUGIN_UTIL.EXECUTE_PLSQL_CODE (
p_plsql_code IN VARCHAR2);
Table 19-8 describes the parameters available in the EXECUTE_PLSQL_CODE procedure.
Table 19-8 EXECUTE_PLSQL_CODE Parameters
| Parameter | Description |
|---|---|
|
|
PL/SQL code to be executed. |
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;
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.
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;
Table 19-9 describes the parameters available in the GET_DATA function signature 1.
Table 19-9 GET_DATA Function Signature 1Parameters
| Parameters | Description |
|---|---|
|
|
SQL statement used for the lookup. |
|
|
Minimum number of return columns. |
|
|
Maximum number of return columns. |
|
|
In case an error is returned, this is the name of the page item or report column used to display the error message. |
|
|
Must be one of the c_search_* constants. They are as follows: |
|
|
Number of the column used to restrict the SQL statement. Must be within the |
|
|
Value used to restrict the query. |
|
|
Start query at the specified row. All rows before the specified row are skipped. |
|
|
Maximum number of return rows allowed. |
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 |
|---|---|
|
|
Table of |
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;
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.
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;
Table 19-11 describes the parameters available for GET_DATA function signature 2.
Table 19-11 GET_DATA Function Signature 2 Parameters
| Parameters | Description |
|---|---|
|
|
SQL statement used for the lookup. |
|
|
Minimum number of return columns. |
|
|
Maximum number of return columns. |
|
|
In case an error is returned, this is the name of the page item or report column used to display the error message. |
|
|
Must be one of the c_search_* constants. They are as follows: |
|
|
This is the column name used to restrict the SQL statement. |
|
|
Value used to restrict the query. |
|
|
Start query at the specified row. All rows before the specified row are skipped. |
|
|
Maximum number of return rows allowed. |
Table 19-12 describes the return value by the GET_DATA function signature 2.
Table 19-12 GET_TABLE Function Signature 2
| Parameter | Description |
|---|---|
|
|
Table of |
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;
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.
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;
Table 19-13 describes the parameters available in the GET_DATA2 function.
Table 19-13 GET_DATA2 Parameters
| Parameter | Description |
|---|---|
|
|
SQL statement used for the lookup. |
|
|
Minimum number of return columns. |
|
|
Maximum number of return columns. |
|
|
If provided, checks to make sure the data type for each column matches the specified data type in the array. Use the constants |
|
|
In case an error is returned, this is the name of the page item or report column used to display the error message. |
|
|
Must be one of the c_search_* constants. They are as follows: |
|
|
Number of the column used to restrict the SQL statement. Must be within the |
|
|
Value used to restrict the query. |
|
|
Start query at the specified row. All rows before the specified row are skipped. |
|
|
Maximum number of return rows allowed. |
Table 19-14 describes the return value by the GET_DATA2 function.
| Return | Description |
|---|---|
|
|
Table of |
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;
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.
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;
Table 19-15 describes the parameters available in the GET_DATA2 function signature 2.
Table 19-15 GET_DATA2 Function Signature 2
| Parameter | Description |
|---|---|
|
|
SQL statement used for the lookup. |
|
|
Minimum number of return columns. |
|
|
Maximum number of return columns. |
|
|
If provided, checks to make sure the data type for each column matches the specified data type in the array. Use the constants |
|
|
In case an error is returned, this is the name of the page item or report column used to display the error message. |
|
|
Must be one of the c_search_* constants. They are as follows: |
|
|
The column name used to restrict the SQL statement. |
|
|
Value used to restrict the query. |
|
|
Start query at the specified row. All rows before the specified row are skipped. |
|
|
Maximum number of return rows allowed. |
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 |
|---|---|
|
|
Table of |
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;
This function gets the display lookup value for the value specified in p_search_string.
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;
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 |
|---|---|
|
|
SQL statement used for the lookup. |
|
|
Minimum number of return columns. |
|
|
Maximum number of return columns. |
|
|
In case an error is returned, this is the name of the page item or report column used to display the error message. |
|
|
Number of the column returned from the SQL statement. Must be within the |
|
|
Number of the column used to restrict the SQL statement. Must be within the |
|
|
Value used to restrict the query. |
|
|
If set to |
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 |
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;
This function looks up all the values provided in the p_search_value_list instead of just a single value lookup.
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;
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 |
|---|---|
|
|
SQL statement used for the lookup. |
|
|
Minimum number of return columns. |
|
|
Maximum number of return columns. |
|
|
In case an error is returned, this is the name of the page item or report column used to display the error message. |
|
|
Number of the column returned from the SQL statement. Must be within the |
|
|
Number of the column used to restrict the SQL statement. Must be within the |
|
|
Array of values to look up. |
|
|
If set to |
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 |
|---|---|
|
|
List of VARCHAR2 indexed by pls_integer. For each entry in |
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;
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.
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;
Table 19-21 describes the available parameters for GET_ELEMENT_ATTRIBUTES function.
Table 19-21 GET_ELEMENT_ATTRIBUTES Function Parameters
| Parameters | Description |
|---|---|
|
|
This is the p_item parameter of your plug-in function. |
|
|
This is the value which has been return by apex_plugin.get_input_name_or_page_item |
|
|
Default CSS class which which should be contained in the result string. |
|
|
If set to TRUE then the id attribute is also contained in the result string. |
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||'" '||
' />');
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.
APEX_PLUGIN_UTIL.GET_PLSQL_EXPRESSION_RESULT (
p_plsql_expression IN VARCHAR2)
RETURN VARCHAR2;
Table 19-22 describes the parameters available in the GET_PLSQL_EXPRESSION_RESULT function.
Table 19-22 GET_PLSQL_EXPRESSION_RESULT Parameters
| Parameter | Description |
|---|---|
|
|
A PL/SQL expression that returns a string. |
Table 19-23 describes the return value by the function GET_PLSQL_EXPRESSION_RESULT.
Table 19-23 GET_PLSQL_EXPRESSION_RESULT Return
| Return | Description |
|---|---|
|
|
String result value returned by the PL/SQL Expression. |
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 );
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.
APEX_PLUGIN_UTIL.GET_PLSQL_FUNCTION_RESULT (
p_plsql_function IN VARCHAR2)
RETURN VARCHAR2;
Table 19-24 describes the parameters available in the GET_PLSQL_FUNCTION_RESULT function.
Table 19-24 GET_PLSQL_FUNCTION_RESULT Parameters
| Parameter | Description |
|---|---|
|
|
A PL/SQL function block that returns a result of type string. |
Table 19-25 describes the return value by the function GET_PLSQL_FUNCTION_RESULT.
Table 19-25 GET_PLSQL_FUNCTION_RESULT Return
| Return | Description |
|---|---|
|
|
String result value returned by the PL/SQL function block. |
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 );
This function returns the position in the list where p_value is stored. If it is not found, null is returned.
APEX_PLUGIN_UTIL.GET_POSITION_IN_LIST(
p_list IN apex_application_global.vc_arr2,
p_value IN VARCHAR2)
RETURN NUMBER;
Table 19-26 describes the parameters available in the GET_POSITION_IN_LIST function.
Table 19-26 GET_POSITION_IN_LIST Parameters
| Parameter | Description |
|---|---|
|
|
Array of type |
|
|
Value located in the |
Table 19-27 describes the return value by the GET_POISTION_IN_LIST function.
Table 19-27 GET_POSITION_IN_LIST Return
| Return | Description |
|---|---|
|
|
Returns the position of |
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;
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.
APEX_PLUGIN_UTIL.GET_SEARCH_STRING(
p_search_type IN VARCHAR2,
p_search_string IN VARCHAR2)
RETURN VARCHAR2;
Table 19-28 describes the parameters available in the GET_SEARCH_STRING function.
Table 19-28 GET_SEARCH_STRING Parameters
| Parameter | Description |
|---|---|
|
|
Type of search when used with |
|
|
Search string used for the search with |
Table 19-29 describes the return value by the function GET_SEARCH_STRING.
Table 19-29 GET_SEARCH_STRING Return
| Return | Description |
|---|---|
|
|
Returns |
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 ) );
This function returns TRUE if both values are equal and FALSE if not. If both values are NULL, TRUE is returned.
APEX_PLUGIN_UTIL.IS_EQUAL (
p_value1 IN VARCHAR2
p_value2 IN VARCHAR2)
RETURN BOOLEAN;
Table 19-30 describes the parameters available in the IS_EQUAL function.
Table 19-30 IS_EQUAL Parameters
| Parameter | Description |
|---|---|
|
|
First value to compare. |
|
|
Second value to compare. |
Table 19-31 describes the return value by the function IS_EQUAL.
| Return | Description |
|---|---|
|
|
Returns |
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;
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.
APEX_PLUGIN_UTIL.PAGE_ITEM_NAMES_TO_JQUERY (
p_page_item_names IN VARCHAR2)
RETURN VARCHAR2;
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 |
|---|---|
|
|
Comma delimited list of page item names. |
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 |
|---|---|
|
|
Transforms the page items specified in |
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)||
'});' );
This procedure outputs a SPAN tag for a display only field.
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');
Table 19-34 describes the parameters available in the PRINT_DISPLAY_ONLY procedure.
Table 19-34 PRINT_DISPLAY_ONLY Parameter
| Parameter | Description |
|---|---|
|
|
Name of the page item. This parameter should be called with |
|
|
Text to be displayed. |
|
|
If set to TRUE line breaks in |
|
|
Additional attributes added to the SPAN tag. |
|
|
Postfix which is getting added to the value in |
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 );
This procedure outputs the value in an escaped form and chunks big strings into smaller outputs.
APEX_PLUGIN_UTIL.PRINT_ESCAPED_VALUE (
p_value IN VARCHAR2);
Table 19-35 describes the parameters available in the PRINT_ESCAPED_VALUE procedure.
Table 19-35 PRINT_ESCAPED_VALUE Parameter
| Parameter | Description |
|---|---|
|
|
Text which should be escaped and then printed to the HTTP buffer. |
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('">');
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.
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);
Table 19-36 describes the parameters available in the PRINT_HIDDEN_IF_READONLY procedure.
Table 19-36 PRINT_HIDDEN_IF_READONLY Parameters
| Parameter | Description |
|---|---|
|
|
Name of the page item. For this parameter the |
|
|
Current value of the page item. For this parameter |
|
|
Is the item rendered readonly. For this parameter |
|
|
Is the item rendered in printer friendly mode. For this parameter |
|
|
Used to generate the ID attribute of the hidden field. It is build based on |
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 );
This procedure outputs a standard HTTP header for a JSON output.
APEX_PLUGIN_UTIL.PRINT_JSON_HTTP_HEADER;
None.
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(']');
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);
Table 19-37 describes the parameters available in the PRINT_LOV_AS_JSON procedure.
Table 19-37 PRINT_LOV_AS_JSON Parameters
| Parameter | Description |
|---|---|
|
|
A SQL statement which returns two columns from the SELECT. |
|
|
The name of the page item or report column that is used in case an error is displayed. |
|
|
If set to |
|
|
If set to TRUE, |
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 );
This procedure outputs an OPTION tag.
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);
Table 19-38 describes the parameters available in the PRINT_OPTION procedure.
Table 19-38 PRINT_OPTION Parameters
| Parameter | Description |
|---|---|
|
|
Text which is displayed by the option. |
|
|
Value which is set when the option is picked. |
|
|
Set to TRUE if the selected attribute should be set for this option. |
|
|
Additional HTML attributes which should be set for the OPTION tag. |
|
|
Set to TRUE if special characters in |
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>');
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.
apex_plugin_util.replace_substitutions (
p_value in varchar2,
p_escape in boolean default true )
return varchar2;
Table 19-39 describes the parameters available in the REPLACE_SUBSTITUTION function.
Table 19-39 REPLACE_SUBSTITUTION Parameters
| Parameter | Description |
|---|---|
|
|
This value is a string which can contain several |
|
|
If set to |
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 );