24.35 OPEN_QUERY_CONTEXT Function Signature 1

This function opens a query context for a local database, remote database, or Web Source Module.

Syntax

FUNCTION 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_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_aggregation             IN t_aggregation            DEFAULT c_empty_aggregation,
  --
  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 24-30 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 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_aggregation Aggregation (GROUP BY, DISTINCT) to apply on top of the query.
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/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
    --
        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;