OPEN_QUERY_CONTEXT Function Signature 1

Opens a query context for a local database, remote database, or Rest Data Source, and executes the provided SQL query.

Syntax

APEX_EXEC.OPEN_QUERY_CONTEXT (
    p_location               in apex_exec_api.t_location,
    --
    p_table_owner            IN VARCHAR2                       DEFAULT NULL,
    p_table_name             IN VARCHAR2                       DEFAULT NULL,
    p_where_clause           IN VARCHAR2                       DEFAULT NULL,
    p_match_clause           IN VARCHAR2                       DEFAULT NULL,
    p_columns_clause         IN VARCHAR2                       DEFAULT NULL,
    p_order_by_clause        IN VARCHAR2                       DEFAULT NULL,
    p_include_rowid_column   IN BOOLEAN                        DEFAULT FALSE,
    --
    p_sql_query              IN VARCHAR2                       DEFAULT NULL,
    p_function_body          IN VARCHAR2                       DEFAULT NULL,
    p_function_body_language IN t_language                     DEFAULT c_lang_plsql,
    p_plsql_function_body    IN VARCHAR2                       DEFAULT NULL, -- Deprecated:
    p_optimizer_hint         IN VARCHAR2                       DEFAULT NULL,
    --
    p_server_static_id       IN VARCHAR2                       DEFAULT NULL,
    --
    p_module_static_id       IN VARCHAR2                       DEFAULT NULL,
    p_web_src_parameters     IN t_parameters                   DEFAULT c_empty_parameters,
    p_external_filter_expr   IN VARCHAR2                       DEFAULT NULL,
    p_external_order_by_expr IN VARCHAR2                       DEFAULT NULL,
    --
    p_sql_parameters         IN t_parameters                   DEFAULT c_empty_parameters,
    p_auto_bind_items        IN BOOLEAN                        DEFAULT TRUE,
    --
    p_columns                IN t_columns                      DEFAULT c_empty_columns,
    --
    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_control_break          IN t_control_break                DEFAULT c_empty_control_break,
    --
    p_post_process_type      IN t_post_processing              DEFAULT NULL,
    --
    p_first_row              IN NUMBER                         DEFAULT NULL,
    p_max_rows               IN NUMBER                         DEFAULT NULL,
    --
    p_total_row_count        IN BOOLEAN                        DEFAULT FALSE,
    p_total_row_count_limit  IN NUMBER                         DEFAULT NULL,
    --
    p_supports_binary_number IN BOOLEAN                        DEFAULT FALSE,
    --
    p_array_column_name      IN VARCHAR2                       DEFAULT NULL,
    --
    p_duality_view_static_id IN VARCHAR2                       DEFAULT NULL,
    p_json_source_static_id  IN VARCHAR2                       DEFAULT NULL )
    RETURN t_context;

Parameters

Parameter	Description
`p_location`	Location to open the query context for. Can be local database, remote database, or Rest Data Source. Use `c_location_*` constants.
`p_table_owner`	Table owner when query type TABLE is used.
`p_table_name`	Table name when query type TABLE is used.
`p_where_clause`	Where clause to append when query types TABLE or GRAPH is used.
`p_match_clause`	Match clause to append when query type GRAPH is used.
`p_columns_clause`	Columns clause to append when query type GRAPH is used.
`p_order_by_clause`	Order by clause to append when query type TABLE is used.
`p_include_rowid_column`	Default `FALSE`. Add the `ROWID` column to the `SELECT` list when query type `TABLE` is used.
`p_sql_query`	SQL Query to execute when query type `SQL Query` is used.
`p_function_body`	Function body returning SQL query.
`p_function_body_language`	Programming language used for `p_function_body`. Use `c_lang_*` constants.
`p_plsql_function_body`	Deprecated. Use `p_function_body` instead.
`p_optimizer_hint`	Optimizer hint to be applied to the most outer SQL query generated by APEX.
`p_server_static_id`	Static ID of the Remote Server when REST-enabled SQL is used (such as when `p_location` uses `c_location_remote_db`.
`p_module_static_id`	Static ID of the REST Data Source (such as when `p_location` uses `c_location_rest_source`.
`p_web_src_parameters`	Parameters to be passed to a REST Data Source.
`p_external_filter_expr`	External filter expression to be passed to a REST Data Source.
`p_external_order_by_expr`	External order by expression to be passed to a REST Data Source.
`p_sql_parameters`	Additional bind variables to be used for the SQL query.
`p_auto_bind_items`	Whether to auto-bind APEX items (page and application items).
`p_columns`	Columns to be selected.
`p_filters`	Filters to be passed to the query context.
`p_order_bys`	Order by expressions to be passed to the query context.
`p_aggregation`	Aggregation (`GROUP BY`, `DISTINCT`) to apply on top of the query.
`p_control_break`	Whether to return control breaks when looping trough the context data.
`p_post_process_type`	Type of post processing to be applied to the REST Data Source result data. Use `c_postprocess_*`constants.
`p_first_row`	First row to be fetched from the result set.
`p_max_rows`	Maximum amount of rows to be fetched.
`p_total_row_count`	Whether to determine the total row count.
`p_total_row_count_limit`	Upper boundary for total row count computation.
`p_supports_binary_number`	Whether to return BINARY NUMBER columns as `c_data_type_binary_number` instead of `c_data_type_number`.
`p_array_column_name`	Name of an array column within the REST Source data profile.
`p_duality_view_static_id`	Static ID of the Duality View Source.
`p_json_source_static_id`	Static ID of the JSON Source.

Returns

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

Example

The following example executes a query and prints out the result set. This example code can be used within a Execute PL/SQL region.

DECLARE
    l_context apex_exec.t_context;

    l_idx_empno    pls_integer;
    l_idx_ename    pls_integer;
    l_idx_job      pls_integer;
    l_idx_hiredate pls_integer;
    l_idx_mgr      pls_integer;
    l_idx_sal      pls_integer;
    l_idx_comm     pls_integer;
    l_idx_deptno   pls_integer;

BEGIN
    l_context := apex_exec.open_query_context(
        p_location          => apex_exec.c_location_local_db,
        p_sql_query         => 'select * from emp' );

    l_idx_empno    := apex_exec.get_column_position( l_context, 'EMPNO');
    l_idx_ename    := apex_exec.get_column_position( l_context, 'ENAME');
    l_idx_job      := apex_exec.get_column_position( l_context, 'JOB');
    l_idx_hiredate := apex_exec.get_column_position( l_context, 'HIREDATE');
    l_idx_mgr      := apex_exec.get_column_position( l_context, 'MGR');
    l_idx_sal      := apex_exec.get_column_position( l_context, 'SAL');
    l_idx_comm     := apex_exec.get_column_position( l_context, 'COMM');
    l_idx_deptno   := apex_exec.get_column_position( l_context, 'DEPTNO');

    WHILE apex_exec.next_row( l_context ) LOOP
        sys.dbms_output.put_line( 'EMPNO: ' || apex_exec.get_number  ( l_context, l_idx_empno    ) );
        sys.dbms_output.put_line( 'ENAME: ' || apex_exec.get_varchar2( l_context, l_idx_ename    ) );
        sys.dbms_output.put_line( 'MGR:   ' || apex_exec.get_number  ( l_context, l_idx_mgr      ) );

    END LOOP;

    apex_exec.close( l_context );
    RETURN;
EXCEPTION
    WHEN others THEN
        apex_exec.close( l_context );
        RAISE;
END;