GET_DATA2 Function Signature 1

This function executes the specified SQL query (optionally restricted by the provided search string) 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 apex_application_global.vc_arr2 DEFAULT c_empty_data_type_list,
    p_component_name   IN VARCHAR2,
    p_search_type      IN VARCHAR2,
    p_search_column_no IN VARCHAR2,
    p_search_string    IN VARCHAR2,
    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 following `c_search_*` constants: `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` through `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.
`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

Return	Description
`t_column_list`	Table of `t_column_values` indexed by column number.

Example 1

In the following example, a simple item type plug-in rendering function executes the LOV defined for the page item and performs a case sensitive LIKE filtering with the current value of the page item. The result then generates as an HTML list. Here, the first column of the LOV SQL statement is checked if it is VARCHAR2 and the second is 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
    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;

Example 2

In the following example, a simple region type plug-in rendering function executes the SQL query defined for the region. The result generates as an HTML list. This example demonstrates the advanced handling of object type columns like SDO_GEOMETRY.

function render (
    p_region in apex_plugin.t_region,
    p_plugin in apex_plugin.t_plugin,
    p_is_printer_friendly in boolean )
    return apex_plugin.t_region_render_result
IS
    l_column_value_list apex_plugin_util.t_column_value_list2;
    l_geometry sdo_geometry;
    l_value varchar2(32767);
    l_dummy pls_integer;
BEGIN
    l_column_value_list :=
        apex_plugin_util.get_data2 (
            p_sql_statement => p_region.source,
            p_min_columns => 1,
            p_max_columns => null,
            p_component_name => p_region.name );
   --
   sys.htp.p('<ul>');
   FOR row in 1 .. l_column_value_list(1).value_list.count LOOP

       sys.htp.p('<li>');
       
       FOR col in 1 .. l_column_value_list.count LOOP
            IF l_column_value_list(col).data_type = 'SDO_GEOMETRY' THEN

               -- Object Type columns are always returned using ANYDATA and we have to
               -- use GETOBJECT to transform them back into the original object type
               l_dummy := l_column_value_list(col).value_list(row).anydata_value.getobject(
l_geometry );
               l_value := '( type=' || l_geometry.sdo_gtype || ' srid=' || l_geometry.sdo_srid ||
                          case when l_geometry.sdo_point is not null THEN
                               ',x=' || l_geometry.sdo_point.x ||
                               ',y=' || l_geometry.sdo_point.y ||
                               ',z=' || l_geometry.sdo_point.z
                          END ||
                          ' )';
               ELSE
                          l_value := apex_plugin_util.get_value_as_varchar2(
                                  p_data_type => l_column_value_list(col).data_type,
                                  p_value => l_column_value_list(col).value_list(row) );
               END IF;

               sys.htp.p( case when col > 1 then ' - ' END || l_value );
          END LOOP;
          
          sys.htp.p('<li>');
      END LOOP;
      sys.htp.p('<ul>');

      RETURN null;
END;