14.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 14-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

Page 230 of 721