28.13 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. 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_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

Return

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;

The following example is a simple region type plug-in rendering function which executes the SQL query defined for the region. The result is then generated as a 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;