14.20 OPEN_QUERY_CONTEXT Function

This function opens a query context for a local database, remote database or a web source module.

Syntax

function open_query_context(
    p_location               in wwv_flow_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_order_by_clause        in varchar2                       default null,
    p_include_rowid_column   in boolean                        default false,
    --
    p_sql_query              in varchar2                       default null,
    p_plsql_function_body    in varchar2                       default null,
    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_first_row              in pls_integer                    default null,
    p_max_rows               in pls_integer                    default null,
    --
    p_total_row_count        in boolean                        default false,
    p_total_row_count_limit  in number                         default null ) return t_context;

Parameters

Table 14-18 OPEN_QUERY_CONTEXT Function Parameters

Parameter	Description
`p_location`	Location to open the query context for. Can be local database , remote database or Web Source Module. Use Use the `C_LOCATION_LOCAL_DB`, `C_LOCATION_REMOTE_DB` or `C_LOCATION_WEB_SOURCE` constants.
`p_module_static_id`	Static ID of the Web Source Module, when `C_LOCATION_WEB_SOURCE` has been used for `p_location`.
`p_server_static_id`	Static ID of the Remote Server, when `C_LOCATION_REMOTE_DB` has been used for `p_location`
`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 type `TABLE` is used.
`p_order_by_clause`	Order by clause to append when query type `TABLE` is used.
`p_include_rowid_column`	Add the `ROWID` column to the `SELECT` list when query type `TABLE` is used. Defaults to `false`.
`p_sql_query`	SQL Query to execute when query type SQL Query is used.
`p_plsql_function_body`	PL/SQL function body returning SQL query.
`p_optimizer_hint`	Optimizer hint to be applied to the most outer SQL query generated by APEX.
`p_external_filter_expr`	External filter expression to be passed to a Web Source Module.
`p_external_order_by_expr`	External order by expression to be passed to a Web Source Module.
`p_web_src_parameters`	Parameters to be passed to a web source module.
`p_auto_bind_items`	Whether to auto-bind APEX items (page and application items).
`p_sql_parameters`	Additional bind variables to be used for the SQL query.
`p_filters`	Filters to be passed to the query context.
`p_order_bys`	Order by expressions to be passed to the query context.
`p_columns`	Columns to be selected .
`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.

Returns

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

Example

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

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
--
        htp.p( 'EMPNO: ' || apex_exec.get_number  ( l_context, l_idx_empno    ) ); 
        htp.p( 'ENAME: ' || apex_exec.get_varchar2( l_context, l_idx_ename    ) );
        htp.p( '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;

Parent topic: APEX_EXEC