22.39 OPEN_REST_SOURCE_QUERY Function

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

Syntax

FUNCTION OPEN_REST_SOURCE_QUERY (
    p_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_aggregation            IN t_aggregation    DEFAULT c_empty_aggregation,
    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 22-37 OPEN_REST_SOURCE_QUERY Parameters

Parameter	Description
p_static_id	Static ID of the REST Data Source to invoke.
p_parameters	Parameter values to be passed to the data source.
p_filters	Filters to be passed to the data source.
p_order_bys	Order by expressions to be passed to the data source.
p_aggregation	Aggregation (GROUP BY, DISTINCT) to apply on top of the query.
p_columns	Columns to be selected from the data source.
p_first_row	First row to be fetched from the data source.
p_max_rows	Maximum amount of rows to be fetched from the data 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 attribute "allow fetch all rows" equals Yes).

Returns

The context object representing a cursor for the REST Data Source query

Example

The following example assumes a REST Data Source 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_rest_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;