27.53 OPEN_WEB_SOURCE_QUERY Function (Deprecated)
Note:
This function is deprecated and will be removed in a future release. UseOPEN_REST_SOURCE_QUERY
instead. See OPEN_REST_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_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,
--
p_array_column_name IN VARCHAR2 DEFAULT NULL )
RETURN t_context;
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_aggregation |
Aggregation (GROUP BY , DISTINCT ) to apply on top of the query.
|
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 attribute "allow fetch all rows" equals Yes ).
|
p_array_column_name |
Name of an array column within the REST Source data profile. |
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