13.21 OPEN_WEB_SOURCE_QUERY Function

This function opens a Web Source query context. Based on the provided web source static ID, the operation matched to the FETCH_COLLECTION database operation will be selected.

Syntax

function open_web_source_query(
    p_module_static_id       in varchar2,
    p_parameters             in t_parameters default c_empty_parameters,
    --
    p_filters                in t_filters    default c_empty_filters,
    p_order_bys              in t_order_bys  default c_empty_order_bys,
    p_columns                in t_columns    default c_empty_columns,
    --
    p_first_row              in pls_integer  default null,
    p_max_rows               in pls_integer  default null,
    --
    p_external_filter_expr   in varchar2     default null,
    p_external_order_by_expr in varchar2     default null,
    p_total_row_count        in boolean      default false )
    return t_context;

Parameters

Table 13-19 OPEN_WEB_SOURCE_QUERY Function Parameters

Parameter Description

p_module_static_id

Static ID of the web source module to invoke.

p_parameters

Parameter values to be passed to the web source.

p_filters

Filters to be passed to the web source.

p_order_bys

Order by expressions to be passed to the web source.

p_columns

Columns to be selected from the web source.

p_first_row

First row to be fetched from the web source.

p_max_rows

Maximum amount of rows to be fetched from the web source.

p_external_filter_expr

Filter expression to be passed 1:1 to the external web service. Depends on the actual web service being used.

p_external_order_by_expr

Order by expression to be passed 1:1 to the external web service. Depends on the actual web service being used.

p_total_row_count

whether to determine the total row count (only supported when the "allow fetch all rows" attribute is set to Yes).

Returns

The context object representing a "cursor" for the web source query

Example

The following example assumes a Web Source module with the static ID "USGS" to be created in Shared Components, based on the URL endpoint https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_day.geojson. The example invokes the REST service and prints out the result set. This example code could be used within a plug-in or within a "Execute PL/SQL" region.

declare 
    l_context apex_exec.t_context;
    l_filters apex_exec.t_filters;
    l_columns apex_exec.t_columns;

    l_row     pls_integer := 1;

    l_magidx  pls_integer;
    l_titidx  pls_integer;
    l_plcidx  pls_integer;
    l_timidx  pls_integer;
    l_ididx   pls_integer;
begin
    l_context := apex_exec.open_web_source_query(
        p_module_static_id => 'USGS',            
        p_max_rows         => 1000 );

    l_titidx := apex_exec.get_column_position( l_context, 'TITLE' );
    l_magidx := apex_exec.get_column_position( l_context, 'MAG' );
    l_plcidx := apex_exec.get_column_position( l_context, 'PLACE' );
    l_timidx := apex_exec.get_column_position( l_context, 'TIME' );
    l_ididx  := apex_exec.get_column_position( l_context, 'ID' );

    while apex_exec.next_row( l_context ) loop

        htp.p( 'ID:    ' || apex_exec.get_varchar2( l_context, l_ididx  ) );
        htp.p( 'MAG:   ' || apex_exec.get_varchar2( l_context, l_magidx ) );
        htp.p( 'PLACE: ' || apex_exec.get_varchar2( l_context, l_plcidx ) );
        htp.p( 'TITLE: ' || apex_exec.get_varchar2( l_context, l_titidx ) );
        htp.p( 'TIME:  ' || apex_exec.get_varchar2( l_context, l_timidx ) );
     end loop;

     apex_exec.close( l_context );
exception
     when others then
         apex_exec.close( l_context );
     raise;    
end;

Parent topic: APEX_EXEC