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 |
---|---|
|
Location to open the query context for. Can be local database , remote database or Web Source Module. Use Use the |
|
Static ID of the Web Source Module, when |
|
Static ID of the Remote Server, when |
|
Table owner when query type |
|
Table name when query type |
|
Where clause to append when query type |
|
Order by clause to append when query type |
|
Add the |
|
SQL Query to execute when query type SQL Query is used. |
|
PL/SQL function body returning SQL query. |
|
Optimizer hint to be applied to the most outer SQL query generated by APEX. |
|
External filter expression to be passed to a Web Source Module. |
|
External order by expression to be passed to a Web Source Module. |
|
Parameters to be passed to a web source module. |
|
Whether to auto-bind APEX items (page and application items). |
|
Additional bind variables to be used for the SQL query. |
|
Filters to be passed to the query context. |
|
Order by expressions to be passed to the query context. |
|
Columns to be selected . |
|
First row to be fetched from the result set. |
|
Maximum amount of rows to be fetched. |
|
Whether to determine the total row count. |
|
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;
Parent topic: APEX_EXEC