13.5.1.1 Querying REST Source Rows in Code

Use OPEN_REST_SOURCE_QUERY in the APEX_EXEC package to query rows from a REST Data Source. It automatically executes the operation with the Fetch rows Database Action.

To define the data source columns whose values you want to retrieve, use a variable of type APEX_EXEC.T_COLUMNS and call ADD_COLUMN to add one or more column names to it. Then pass it as the p_columns argument value.

To set operation parameters, use a variable of type APEX_EXEC.T_PARAMETERS, call ADD_PARAMETER to add one or more parameters to the list, then pass it as the p_parameters value. Use a while loop with NEXT_ROW as the loop condition to iterate through the results. When done, or when an exception occurs, call CLOSE to clean up context resources.

A simple example that retrieves and iterates over all rows from the Employees (Simple HTTP) REST Data Source is below. Notice the code references the REST Data Source using its Static ID employees_simple_http.

declare
    l_params   apex_exec.t_parameters;
    l_cols     apex_exec.t_columns;
    l_ctx      apex_exec.t_context;
    l_empno    number;
    l_ename    varchar2(255);
    l_hiredate date;
    l_empinfo  apex_t_varchar2;
begin
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'EMPNO');
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'ENAME');
    apex_exec.add_column(
        p_columns     => l_cols,
        p_column_name => 'HIREDATE');
    apex_exec.add_parameter(
        p_parameters => l_params,
        p_name       => 'MY_PARAM',
        p_value      => 1234);
    l_ctx := apex_exec.open_rest_source_query(
                p_static_id  => 'employees_simple_http',
                p_columns    => l_cols,
                p_parameters => l_params);
    while apex_exec.next_row(l_ctx) loop
        l_empno    := apex_exec.get_number(l_ctx,'EMPNO');
        l_ename    := apex_exec.get_varchar2(l_ctx,'ENAME');
        l_hiredate := apex_exec.get_date(l_ctx,'HIREDATE');
        -- Do something here with current row values
    end loop;
    apex_exec.close(l_ctx);
exception
    when others then
        apex_exec.close(l_ctx);
        raise;
end;

OPEN_REST_SOURCE_QUERY accepts other optional arguments that let you:

• Order retrieved rows by passing a APEX_EXEC.T_ORDER_BYS list to p_order_bys
• Filter rows by passing a APEX_EXEC.T_FILTERS list to p_filters
• Retrieve only a p_max_rows-sized page of rows starting with row number p_first_row

For more information, see OPEN_REST_SOURCE_QUERY Function in Oracle APEX API Reference.

Be aware that by default Simple HTTP REST Data Sources perform ordering and filtering in the APEX database after retrieving all rows from the REST API. Other convention-based REST Data Source types automatically delegate filtering, sorting, and pagination to the REST API for an optimized data exchange. APEX offers these smarter REST Data Source types for Fusion Applications, Oracle REST Data Services, and OData.