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 name in the p_search_column_name 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 apex_application_global.vc_arr2 DEFAULT c_empty_data_type_list,
    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_list;

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_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.
`p_auto_bind_items`	Whether to auto-bind APEX items (page and application items).
`p_bind_list`	Additional bind variables to be used for the SQL query.

Return

Parameter	Description
`t_column_list`	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;