41.18 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. This function takes into account character value comparison globalization attributes defines for the application.
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 41-22 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. |
Return
Table 41-23 GET_DATA2 Function Signature 2 Return
Parameter | Description |
---|---|
|
Table of |
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;
Parent topic: APEX_PLUGIN_UTIL