APEX_EXEC is the layer Oracle APEX regions use to read and write data, and it hides where that data lives. The same calls work on the local database, a remote database through REST Enabled SQL, a REST Data Source, a Duality View, or a JSON source. That makes it the right tool whenever PL/SQL must read a REST API through the credentials and settings already defined in your application, or write rows with the same lost-update protection that forms use.
This guide covers APEX_EXEC with tested examples and their real output: query contexts with filters, sorting, and paging; column metadata; bind parameters; REST Data Sources with array columns; DML contexts with error handling and checksums; copying data between sources; and Duality Views. It also points out two behaviors in APEX 26.1 the documentation does not mention.
Quick Reference
| Task | Subprogram |
|---|---|
| Run a query on any location | OPEN_QUERY_CONTEXT |
| Filter and sort | ADD_FILTER, ADD_ORDER_BY |
| Read rows | NEXT_ROW, GET_VARCHAR2, GET_NUMBER, GET_DATE, and the other GET functions, GET_COLUMN_POSITION, GET_TOTAL_ROW_COUNT, HAS_MORE_ROWS, CLOSE |
| Describe columns | GET_COLUMN_COUNT, GET_COLUMNS, GET_COLUMN, GET_DATA_TYPE, DESCRIBE_QUERY, ADD_COLUMN, COLUMN_EXISTS |
| Bind parameters and run PL/SQL | ADD_PARAMETER, GET_PARAMETER functions, EXECUTE_PLSQL |
| Quote literals and names | ENQUOTE_LITERAL, ENQUOTE_NAME |
| Use REST Data Sources | OPEN_REST_SOURCE_QUERY, OPEN_ARRAY, NEXT_ARRAY_ROW, CLOSE_ARRAY, EXECUTE_REST_SOURCE, PURGE_REST_SOURCE_CACHE |
| Insert, update, and delete | OPEN_LOCAL_DML_CONTEXT and its siblings, ADD_DML_ROW, SET_VALUE, EXECUTE_DML, status functions |
| Prevent lost updates | GET_ROW_VERSION_CHECKSUM, SET_ROW_VERSION_CHECKSUM |
| Copy between sources | COPY_DATA |
How to Run These Examples
Every APEX_EXEC call needs an APEX session, because data sources and credentials belong to an application. In a script, create one first with APEX_SESSION.CREATE_SESSION for application 200, page 1, as shown in the guide to creating APEX sessions and managing session state from PL/SQL. Run the examples as your workspace schema with server output switched on. The output under each example is exactly what the database printed in Oracle APEX 26.1.
The local examples read tables of the Orbit Outfitters sample schema, including orb_products, orb_stores, orb_orders, orb_promotions, and the duality view orb_order_dv, all available from the orb_tables repository on GitHub. Two examples also use shared components of the test application that you would create in your own: a REST Data Source with the static ID city-geocoding, pointing to the Open-Meteo geocoding API with a query parameter named name, and a Duality View source with the static ID order-documents on ORB_ORDER_DV, whose documents are orders with their lines in a LINES array. REST Data Sources themselves are covered in the guide to data sources, data loads, and duality views.
Querying: Contexts and Rows
A context, of type apex_exec.t_context, is a handle to an open query or DML operation. Open it, loop with NEXT_ROW, read the values, and always CLOSE it, including in an exception handler, or the cursor stays open for the rest of the session.
OPEN_QUERY_CONTEXT
Opens and runs a query on any location. p_location is apex_exec.c_location_local_db, c_location_remote_db (with p_server_static_id), c_location_rest_source (with p_module_static_id), c_location_duality_view (with p_duality_view_static_id), or c_location_json_source (with p_json_source_static_id). The query is a table (p_table_owner, p_table_name, p_where_clause, p_order_by_clause), a SQL query (p_sql_query), or a function body that returns one (p_function_body, in PL/SQL or JavaScript). p_match_clause and p_columns_clause are for SQL property graphs.
Syntax:
apex_exec.open_query_context(p_location in 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_function_body in varchar2 default null, p_function_body_language in t_language default c_lang_plsql,
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_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_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_contextp_first_row and p_max_rows page through the result, and p_total_row_count set to true counts all rows as well. With p_auto_bind_items, the default, bind variables such as :P10_STATUS take the values of page items; p_sql_parameters passes others. A second signature, taking p_columns, p_filters, p_component_sql, and p_use_region_filters_orderbys, is for region plug-ins: it opens the data source of the region being rendered.
ADD_FILTER and ADD_ORDER_BY
ADD_FILTER adds a condition to a t_filters array. APEX applies filters in SQL for local and remote sources, and passes them to REST Data Sources that support them, or applies them locally after fetching. ADD_ORDER_BY adds a sort column, by name or position, with c_order_asc or c_order_desc, and c_order_nulls_first or c_order_nulls_last.
Syntax:
apex_exec.add_filter(p_filters in out nocopy t_filters, p_filter_type in t_filter_type, p_column_name in varchar2,
p_value in varchar2 | number | date | timestamp[ with (local) time zone] | boolean
| p_from_value, p_to_value | p_values in apex_t_varchar2 | apex_t_number,
p_null_result in boolean default false, p_is_case_sensitive in boolean default true)
apex_exec.add_filter(p_filters, p_filter_type, p_column_name, p_interval in pls_integer, p_interval_type in varchar2)
apex_exec.add_filter(p_filters, p_search_columns in t_columns, p_is_case_sensitive, p_value) -- row search
apex_exec.add_filter(p_filters, p_sql_expression in varchar2)
apex_exec.add_order_by(p_order_bys in out nocopy t_order_bys, p_column_name in varchar2 | p_position in pls_integer,
p_direction in t_order_direction default c_order_asc, p_order_nulls in t_order_nulls default null)| Filter type constants | Condition |
|---|---|
| c_filter_eq, _not_eq, _gt, _gte, _lt, _lte | Compare with p_value. |
| c_filter_null, _not_null, _true, _false | No value needed. |
| c_filter_starts_with, _ends_with, _contains, _like, _regexp, and their _not_ forms | Text patterns. |
| c_filter_in, _not_in | A list in p_values. |
| c_filter_between, _not_between (_lbe and _ube for exclusive bounds) | p_from_value and p_to_value. |
| c_filter_last, _next, _not_last, _not_next | p_interval units of p_interval_type: c_filter_int_type_year, _month, _week, _day, _hour, or _minute. |
| c_filter_search | Search several columns for a text. |
| c_filter_sql_expression | A SQL condition, for local sources only. |
| c_filter_oracletext, c_filter_dbms_search, c_filter_sdo_filter, _sdo_anyinteract, _sdo_relate, c_filter_vector_type | Oracle Text, Ubiquitous Search, spatial, and vector search, each with its own overloads. |
Reading Rows: NEXT_ROW, the GET Functions, and CLOSE
NEXT_ROW moves to the next row and returns false after the last one. The GET functions read a column of the current row by name or position: GET_VARCHAR2, GET_NUMBER, GET_BINARY_NUMBER, GET_DATE, GET_TIMESTAMP, GET_TIMESTAMP_TZ, GET_TIMESTAMP_LTZ, GET_INTERVALD2S, GET_INTERVALY2M, GET_CLOB, GET_BLOB, GET_BOOLEAN, GET_SDO_GEOMETRY, GET_ANYDATA, and GET_ARRAY (a json_array_t). GET_COLUMN_POSITION finds a column's position once, so the loop can read by position; with p_is_required true it raises an error if the column is missing. GET_TOTAL_ROW_COUNT returns the count requested with p_total_row_count, and HAS_MORE_ROWS, after the loop, tells whether there were more rows than p_max_rows. CLOSE closes the context.
Syntax:
apex_exec.next_row(p_context in t_context) return boolean
apex_exec.get_varchar2 | get_number | get_date | ... (p_context in t_context, p_column_idx in pls_integer | p_column_name in varchar2)
apex_exec.get_column_position(p_context in t_context, p_column_name in varchar2, p_attribute_label in varchar2 default null,
p_is_required in boolean default false, p_data_type in varchar2 default null) return pls_integer
apex_exec.get_total_row_count | has_more_rows(p_context in t_context) return number | boolean
apex_exec.close(p_context in t_context)This example needs a session of application 200, page 1.
Example:
declare
l_filters apex_exec.t_filters;
l_order apex_exec.t_order_bys;
l_context apex_exec.t_context;
l_name_pos pls_integer;
begin
-- filters and sort order are added to the query as APEX regions add theirs
apex_exec.add_filter(p_filters => l_filters, p_filter_type => apex_exec.c_filter_starts_with,
p_column_name => 'SKU', p_value => 'TNT');
apex_exec.add_filter(p_filters => l_filters, p_filter_type => apex_exec.c_filter_between,
p_column_name => 'UNIT_PRICE', p_from_value => 100, p_to_value => 300);
apex_exec.add_order_by(p_order_bys => l_order, p_column_name => 'UNIT_PRICE',
p_direction => apex_exec.c_order_desc);
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db,
p_sql_query => 'select sku, product_name, unit_price, launch_date from orb_products',
p_filters => l_filters,
p_order_bys => l_order,
p_max_rows => 3,
p_total_row_count => true);
dbms_output.put_line('total rows: ' || apex_exec.get_total_row_count(l_context));
l_name_pos := apex_exec.get_column_position(l_context, 'PRODUCT_NAME');
while apex_exec.next_row(l_context) loop
dbms_output.put_line(apex_exec.get_varchar2(l_context, 'SKU') || ' '
|| rpad(apex_exec.get_varchar2(l_context, l_name_pos), 30) -- by position
|| to_char(apex_exec.get_number(l_context, 'UNIT_PRICE'), '990.00') || ' '
|| to_char(apex_exec.get_date(l_context, 'LAUNCH_DATE'), 'DD-MON-YYYY'));
end loop;
apex_exec.close(l_context);
exception
when others then
apex_exec.close(l_context); -- always close, also on errors
raise;
end;
/Output:
total rows: 4 TNT-1002 Trailblazer 2-Person Tent 274.99 18-DEC-2024 TNT-1001 Trailblazer 1-Person Tent 239.99 13-JUL-2022 TNT-1003 Basecamp 4-Person Tent 204.99 01-APR-2022
Four products matched, three were returned because of p_max_rows, and the filters were written into the SQL for you. The exception handler closing the context before re-raising is the pattern to copy.
GET_COLUMN_COUNT, GET_COLUMNS, GET_COLUMN, GET_DATA_TYPE, DESCRIBE_QUERY, ADD_COLUMN, and COLUMN_EXISTS
GET_COLUMN_COUNT, GET_COLUMNS, and GET_COLUMN describe the columns of an open context; a column is a t_column record with name, data_type, data_type_length, format_mask, is_primary_key, and more. Data types are numbers, from apex_exec.c_data_type_varchar2 (1), c_data_type_number (2), and c_data_type_date (3) up to c_data_type_vector (18), and GET_DATA_TYPE converts between a number and its name. DESCRIBE_QUERY returns a query's columns without running it, taking the same source parameters as OPEN_QUERY_CONTEXT.
ADD_COLUMN adds a column to a t_columns array, to select only some columns, compute one with p_sql_expression, or describe the columns of a DML context. COLUMN_EXISTS tells whether an array has a column.
Syntax:
apex_exec.add_column(p_columns in out nocopy t_columns, p_column_name in varchar2, p_data_type in t_data_type default null,
p_sql_expression in varchar2 default null, p_format_mask in varchar2 default null,
p_is_primary_key in boolean default false, p_is_query_only in boolean default false,
p_is_returning in boolean default false, p_is_checksum in boolean default false, p_parent_column_path in varchar2 default null)
apex_exec.column_exists(p_columns in t_columns, p_column_name in varchar2, p_parent_column_path in varchar2 default null) return boolean
apex_exec.get_data_type(p_datatype_num in t_data_type) return varchar2
apex_exec.get_data_type(p_datatype in varchar2) return t_data_typeThis example needs a session of application 200, page 1.
Example:
declare
l_context apex_exec.t_context;
l_columns apex_exec.t_columns;
l_column apex_exec.t_column;
begin
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db,
p_sql_query => 'select id, store_name, opened_on, flagship, latitude from orb_stores',
p_max_rows => 0); -- no rows, just the columns
dbms_output.put_line('columns: ' || apex_exec.get_column_count(l_context));
for i in 1 .. apex_exec.get_column_count(l_context) loop
l_column := apex_exec.get_column(l_context, i);
dbms_output.put_line(i || ' ' || rpad(l_column.name, 11)
|| apex_exec.get_data_type(l_column.data_type) || ' (' || l_column.data_type || ')');
end loop;
apex_exec.close(l_context);
dbms_output.put_line('type code of NUMBER: ' || apex_exec.get_data_type('NUMBER'));
-- describe a query without running it
l_columns := apex_exec.describe_query(
p_location => apex_exec.c_location_local_db,
p_sql_query => 'select order_id, order_total, order_date from orb_orders');
for i in 1 .. l_columns.count loop
dbms_output.put_line('described: ' || l_columns(i).name || ' ' ||
apex_exec.get_data_type(l_columns(i).data_type));
end loop;
dbms_output.put_line('has ORDER_DATE: ' ||
case when apex_exec.column_exists(l_columns, 'ORDER_DATE') then 'yes' else 'no' end);
end;
/Output:
columns: 5 1 ID NUMBER (2) 2 STORE_NAME VARCHAR2 (1) 3 OPENED_ON DATE (3) 4 FLAGSHIP BOOLEAN (16) 5 LATITUDE NUMBER (2) type code of NUMBER: 2 described: ORDER_ID NUMBER described: ORDER_TOTAL NUMBER described: ORDER_DATE DATE has ORDER_DATE: yes
A computed column can only use the other columns in the array, because APEX wraps the query and computes the expression outside it. This example also needs a session:
Example:
declare
l_columns apex_exec.t_columns;
l_context apex_exec.t_context;
begin
-- choose the columns, including one computed from the others
apex_exec.add_column(p_columns => l_columns, p_column_name => 'SKU');
apex_exec.add_column(p_columns => l_columns, p_column_name => 'UNIT_PRICE');
apex_exec.add_column(p_columns => l_columns, p_column_name => 'COST_PRICE');
apex_exec.add_column(p_columns => l_columns, p_column_name => 'MARGIN',
p_data_type => apex_exec.c_data_type_number,
p_sql_expression => 'unit_price - cost_price');
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db,
p_sql_query => q'~select * from orb_products where sku like 'KIT-100%' order by sku~',
p_columns => l_columns,
p_max_rows => 3);
while apex_exec.next_row(l_context) loop
dbms_output.put_line(apex_exec.get_varchar2(l_context, 1) || ' margin '
|| apex_exec.get_number(l_context, 'MARGIN'));
end loop;
apex_exec.close(l_context);
end;
/Output:
KIT-1001 margin 8.26 KIT-1002 margin 87.56 KIT-1003 margin 44.52
IS_GROUP_END
With a control break, passed as p_control_break to OPEN_QUERY_CONTEXT, IS_GROUP_END returns whether the current row is the last of its group, which is where to print a subtotal. The group columns must not be null.
Parameters and PL/SQL
ADD_PARAMETER, GET_PARAMETER, and EXECUTE_PLSQL
ADD_PARAMETER adds a name and value to a t_parameters array, with twenty overloads for the data types, for bind variables and for REST Data Source parameters. The GET_PARAMETER functions read one back by its upper-case name: GET_PARAMETER_VARCHAR2, _NUMBER, _BINARY_NUMBER, _DATE, _TIMESTAMP, _TIMESTAMP_TZ, _TIMESTAMP_LTZ, _CLOB, _INTERVAL_D2S, and _INTERVAL_Y2M. EXECUTE_PLSQL runs a PL/SQL block with the parameters as bind variables and writes OUT values back into the array.
Syntax:
apex_exec.add_parameter(p_parameters in out nocopy t_parameters, p_name in varchar2, p_value in varchar2 | number | date | ...)
apex_exec.get_parameter_varchar2(p_parameters in t_parameters, p_name in varchar2, p_format_mask in varchar2 default null) return varchar2
apex_exec.execute_plsql(p_plsql_code in varchar2, p_auto_bind_items in boolean default true,
p_sql_parameters in out t_parameters)This example needs a session of application 200, page 1.
Example:
declare
l_params apex_exec.t_parameters;
begin
apex_exec.add_parameter(l_params, 'STATUS', 'SHIPPED');
apex_exec.add_parameter(l_params, 'SINCE', date '2026-01-01');
apex_exec.add_parameter(l_params, 'CNT', 0); -- OUT binds need a parameter too
apex_exec.add_parameter(l_params, 'TOTAL', 0);
apex_exec.execute_plsql(
p_plsql_code => 'begin
select count(*), sum(order_total) into :CNT, :TOTAL
from orb_orders where status = :STATUS and order_date >= :SINCE;
end;',
p_sql_parameters => l_params);
-- in 26.1 the values come back as strings: read them with get_parameter_varchar2
dbms_output.put_line('orders: ' || apex_exec.get_parameter_varchar2(l_params, 'CNT'));
dbms_output.put_line('total: ' || apex_exec.get_parameter_varchar2(l_params, 'TOTAL'));
dbms_output.put_line('as number: [' || apex_exec.get_parameter_number(l_params, 'TOTAL') || ']');
end;
/Output:
orders: 32 total: 129617.47 as number: []
This is the first 26.1 surprise: every parameter comes back from EXECUTE_PLSQL as a VARCHAR2, so GET_PARAMETER_NUMBER returned null for a number the block set, while GET_PARAMETER_VARCHAR2 returned its text. Read OUT values as strings and convert them yourself. The code must also be a full block, begin ... end;, not a single statement.
ENQUOTE_LITERAL and ENQUOTE_NAME
Quote a value as a SQL literal or an identifier for the database of a source, Oracle by default or MySQL with p_for_database set to apex_exec.c_database_mysql, when you build a query, a where clause, or a filter expression from text.
This example needs a session of application 200, page 1.
Example:
begin
dbms_output.put_line(apex_exec.enquote_literal(q'~O'Brien's Outfitters~'));
dbms_output.put_line(apex_exec.enquote_name('ORB_PRODUCTS'));
dbms_output.put_line(apex_exec.enquote_name('Unit Price'));
dbms_output.put_line(apex_exec.enquote_name('unit_price', p_for_database => apex_exec.c_database_mysql));
end;
/Output:
'O''Brien''s Outfitters' "ORB_PRODUCTS" "Unit Price" `unit_price`
Prefer bind parameters wherever the source supports them; use these only where a value must become part of the text itself.
REST Data Sources
OPEN_REST_SOURCE_QUERY
Opens a query on a REST Data Source, identified by its static ID. p_parameters sets the source's parameters. Filters, sort order, and paging work as for OPEN_QUERY_CONTEXT, and p_external_filter_expr and p_external_order_by_expr are passed to the REST service as they are. p_array_column_name returns one row per element of an array column.
Syntax:
apex_exec.open_rest_source_query(p_static_id in varchar2, p_parameters in t_parameters default c_empty_parameters,
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_columns in t_columns default c_empty_columns, p_external_filter_expr in varchar2 default null,
p_external_order_by_expr in varchar2 default null, 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_array_column_name in varchar2 default null) return t_contextOPEN_ARRAY, NEXT_ARRAY_ROW, HAS_MORE_ARRAY_ROWS, SET_ARRAY_CURRENT_ROW, and CLOSE_ARRAY
A REST Data Source row can hold arrays, such as the postcodes of a city or the lines of an order. OPEN_ARRAY steps into an array column of the current row, NEXT_ARRAY_ROW moves through its elements, which the GET functions then read, and CLOSE_ARRAY steps back out. SET_ARRAY_CURRENT_ROW jumps to an element, and HAS_MORE_ARRAY_ROWS tells whether more elements follow. In 26.1 they work on REST Data Sources only; for a Duality View, use p_array_column_name.
EXECUTE_REST_SOURCE and PURGE_REST_SOURCE_CACHE
EXECUTE_REST_SOURCE runs one operation of a REST Data Source, chosen by HTTP method (plus URL pattern if the method is used more than once) or by the operation's static ID, with the parameters in the array. OUT parameters, such as a response body parameter, are written back into the array. PURGE_REST_SOURCE_CACHE empties the source's cache, for all sessions or only the current one.
Syntax:
apex_exec.execute_rest_source(p_static_id in varchar2, p_operation in varchar2, p_url_pattern in varchar2 default null,
p_parameters in out t_parameters)
apex_exec.execute_rest_source(p_static_id in varchar2, p_operation_static_id in varchar2, p_parameters in out t_parameters)
apex_exec.purge_rest_source_cache(p_static_id in varchar2, p_current_session_only in boolean default false)This example needs a session of application 200, page 1, and the city-geocoding REST Data Source. For the EXECUTE_REST_SOURCE call, the source has an extra OUT parameter named RESPONSE of type Request/Response Body.
Example:
declare
l_params apex_exec.t_parameters;
l_filters apex_exec.t_filters;
l_context apex_exec.t_context;
begin
-- the lab's REST Data Source "city-geocoding" (Open-Meteo) has a query parameter "name"
apex_exec.add_parameter(l_params, 'name', 'Salem');
apex_exec.add_filter(l_filters, apex_exec.c_filter_eq, 'COUNTRY_CODE', 'US'); -- applied locally
l_context := apex_exec.open_rest_source_query(
p_static_id => 'city-geocoding',
p_parameters => l_params,
p_filters => l_filters,
p_max_rows => 3);
while apex_exec.next_row(l_context) loop
dbms_output.put(rpad(apex_exec.get_varchar2(l_context, 'NAME') || ', '
|| apex_exec.get_varchar2(l_context, 'ADMIN1'), 26)
|| to_char(apex_exec.get_number(l_context, 'POPULATION'), '999G990') || ' zip');
-- POSTCODES is an array column: step into it, row by row
apex_exec.open_array(l_context, 'POSTCODES');
while apex_exec.next_array_row(l_context) loop
dbms_output.put(' ' || apex_exec.get_varchar2(l_context, 'POSTCODES2'));
end loop;
apex_exec.close_array(l_context);
dbms_output.new_line;
end loop;
apex_exec.close(l_context);
-- run an operation directly; the OUT parameter RESPONSE receives the response body
apex_exec.execute_rest_source(p_static_id => 'city-geocoding', p_operation => 'GET',
p_parameters => l_params);
dbms_output.put_line(substr(apex_exec.get_parameter_clob(l_params, 'RESPONSE'), 1, 60) || '...');
apex_exec.purge_rest_source_cache(p_static_id => 'city-geocoding'); -- if caching is on
end;
/Output:
Salem, Oregon 175,535 zip 97301 97302 97303 97305 97306 97308 97309 97310 97311 97312 97313 97314
Salem, Virginia 25,432 zip 24153 24155 24157
Salem, Illinois 7,287 zip 62881
{"results":[{"id":1257629,"name":"Salem","latitude":11.65376...The country filter was applied locally after fetching, because this API does not support it, and the postcodes came from stepping into the array column of each row. To call a REST service that has no REST Data Source defined, APEX_WEB_SERVICE is the lower-level alternative.
Changing Data: DML Contexts
A DML context collects rows to insert, update, or delete and writes them in one call. Describe its columns with ADD_COLUMN, marking the primary key with p_is_primary_key, add rows with ADD_DML_ROW, set their values, and call EXECUTE_DML.
Opening a DML Context
| Function | Writes to |
|---|---|
| OPEN_LOCAL_DML_CONTEXT(p_columns, p_query_type, p_table_owner, p_table_name, p_where_clause, p_sql_query, ..., p_dml_table_owner, p_dml_table_name, p_dml_plsql_code, p_lost_update_detection, p_lock_rows, p_lock_plsql_code, p_sql_parameters) | A local table or view. p_query_type is c_query_type_table, c_query_type_sql_query, or c_query_type_func_return_sql; p_dml_table_* or p_dml_plsql_code write somewhere other than where the query reads. |
| OPEN_REMOTE_DML_CONTEXT(p_server_static_id, ...) | A table on a REST Enabled SQL service, with the same parameters. |
| OPEN_REST_SOURCE_DML_CONTEXT(p_static_id, p_parameters, p_columns, p_lost_update_detection, p_fetch_rows_parameters, p_insert_row_parameters, ..., p_array_column_name) | A REST Data Source with insert, update, and delete operations. |
| OPEN_DUALITY_VIEW_DML_CONTEXT(p_static_id, p_array_column_name, p_columns, p_lost_update_detection) | A Duality View source. |
| OPEN_JSON_SOURCE_DML_CONTEXT(p_static_id, p_array_column_name, p_columns, p_lost_update_detection) | A JSON source, meaning a JSON collection table. |
p_lost_update_detection is c_lost_update_none, c_lost_update_implicit (compare checksums of all columns), or c_lost_update_explicit (use a checksum column). p_lock_rows is c_lock_rows_none, c_lock_rows_automatic, or c_lock_rows_plsql.
ADD_DML_ROW, SET_VALUE, SET_NULL, SET_VALUES, EXECUTE_DML, and the Status Functions
ADD_DML_ROW adds a row with an operation, c_dml_operation_insert, _update, or _delete, and makes it current. SET_VALUE sets a column of the current row by name or position, with overloads for every data type; SET_NULL sets it to null; and SET_VALUES copies all values from the current row of a query context. EXECUTE_DML writes the rows, stopping at the first error or, with p_continue_on_error true, trying them all. SET_CURRENT_ROW moves to a row, and HAS_ERROR, GET_DML_STATUS_CODE, and GET_DML_STATUS_MESSAGE report how it went. CLEAR_DML_ROWS removes the rows so the context can be reused.
Syntax:
apex_exec.add_dml_row(p_context in t_context, p_operation in t_dml_operation) apex_exec.set_value(p_context in t_context, p_column_name in varchar2 | p_column_position in pls_integer, p_value in ...) apex_exec.set_null(p_context in t_context, p_column_name in varchar2 | p_column_position in pls_integer) apex_exec.set_values(p_context in t_context, p_source_context in t_context) apex_exec.execute_dml(p_context in t_context, p_continue_on_error in boolean default false) apex_exec.set_current_row(p_context in t_context, p_row_idx in pls_integer) apex_exec.has_error(p_context in t_context) return boolean apex_exec.get_dml_status_code(p_context in t_context) return number apex_exec.get_dml_status_message(p_context in t_context) return varchar2 apex_exec.clear_dml_rows(p_context in t_context)
This example needs a session of application 200, page 1. Before it ran, any promotions 901 and 902 left over from a previous run were deleted. Its second row breaks a check constraint on purpose.
Example:
declare
l_columns apex_exec.t_columns;
l_context apex_exec.t_context;
begin
apex_exec.add_column(l_columns, 'PROMOTION_ID', apex_exec.c_data_type_number, p_is_primary_key => true);
apex_exec.add_column(l_columns, 'PROMOTION_NAME', apex_exec.c_data_type_varchar2);
apex_exec.add_column(l_columns, 'START_DATE', apex_exec.c_data_type_date);
apex_exec.add_column(l_columns, 'END_DATE', apex_exec.c_data_type_date);
apex_exec.add_column(l_columns, 'DISCOUNT_PCT', apex_exec.c_data_type_number);
apex_exec.add_column(l_columns, 'DESCRIPTION', apex_exec.c_data_type_varchar2);
l_context := apex_exec.open_local_dml_context(
p_columns => l_columns,
p_query_type => apex_exec.c_query_type_table,
p_table_name => 'ORB_PROMOTIONS');
for i in 1 .. 2 loop
apex_exec.add_dml_row(l_context, apex_exec.c_dml_operation_insert);
apex_exec.set_value(l_context, 'PROMOTION_ID', 900 + i);
apex_exec.set_value(l_context, 'PROMOTION_NAME', 'API Lab Sale ' || i);
apex_exec.set_value(l_context, 'START_DATE', date '2026-06-01');
apex_exec.set_value(l_context, 'END_DATE', date '2026-06-30' - (i - 1) * 40); -- row 2 ends before it starts
apex_exec.set_value(l_context, 'DISCOUNT_PCT', 10 * i);
apex_exec.set_null (l_context, 'DESCRIPTION');
end loop;
apex_exec.execute_dml(p_context => l_context, p_continue_on_error => true);
for i in 1 .. 2 loop
apex_exec.set_current_row(l_context, i);
dbms_output.put_line('row ' || i || ': status ' || nvl(to_char(apex_exec.get_dml_status_code(l_context)), 'ok')
|| ' ' || regexp_replace(apex_exec.get_dml_status_message(l_context), 'ORA-\d+: '));
end loop;
dbms_output.put_line('has error: ' || case when apex_exec.has_error(l_context) then 'yes' else 'no' end);
-- delete the row that was inserted: a new row set on the same context
apex_exec.clear_dml_rows(l_context);
apex_exec.add_dml_row(l_context, apex_exec.c_dml_operation_delete);
apex_exec.set_value(l_context, 'PROMOTION_ID', 901);
apex_exec.execute_dml(l_context);
apex_exec.close(l_context);
end;
/Output:
row 1: status ok row 2: status -2290 check constraint (ORBIT.ORB_PROMOTIONS_DATES_CK) violated has error: yes
With p_continue_on_error, the good row was inserted and the bad one reported its own error, which is exactly what you need to show per-row errors back to a user.
GET_ROW_VERSION_CHECKSUM and SET_ROW_VERSION_CHECKSUM
Lost-update detection the way forms do it: GET_ROW_VERSION_CHECKSUM returns a checksum of the current row of a query context, and SET_ROW_VERSION_CHECKSUM hands it to a row of a DML context. If the row changed in between, EXECUTE_DML raises an error instead of overwriting someone else's change. GET_ARRAY_ROW_VERSION_CHECKSUM and SET_ARRAY_ROW_VERSION_CHECKSUM do the same for an array element.
This example needs a session of application 200, page 1. It simulates another user's update between the read and the write, and rolls back at the end.
Example:
declare
l_columns apex_exec.t_columns;
l_query apex_exec.t_context;
l_dml apex_exec.t_context;
l_checksum varchar2(4000);
begin
apex_exec.add_column(l_columns, 'PROMOTION_ID', apex_exec.c_data_type_number, p_is_primary_key => true);
apex_exec.add_column(l_columns, 'DISCOUNT_PCT', apex_exec.c_data_type_number);
-- read the row and keep its checksum, as a form does when it loads
l_query := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db, p_table_name => 'ORB_PROMOTIONS',
p_where_clause => 'promotion_id = 1', p_columns => l_columns);
if apex_exec.next_row(l_query) then
l_checksum := apex_exec.get_row_version_checksum(l_query);
dbms_output.put_line('read: ' || apex_exec.get_number(l_query, 'DISCOUNT_PCT') || '% - checksum ' || substr(l_checksum, 1, 12) || '...');
end if;
apex_exec.close(l_query);
update orb_promotions set discount_pct = discount_pct + 1 where promotion_id = 1; -- someone else
l_dml := apex_exec.open_local_dml_context(
p_columns => l_columns, p_query_type => apex_exec.c_query_type_table,
p_table_name => 'ORB_PROMOTIONS', p_lost_update_detection => apex_exec.c_lost_update_implicit);
apex_exec.add_dml_row(l_dml, apex_exec.c_dml_operation_update);
apex_exec.set_value(l_dml, 'PROMOTION_ID', 1);
apex_exec.set_value(l_dml, 'DISCOUNT_PCT', 50);
apex_exec.set_row_version_checksum(l_dml, l_checksum); -- the checksum read before
begin
apex_exec.execute_dml(l_dml);
exception when others then
dbms_output.put_line('update: ' || regexp_replace(sqlerrm, 'ORA-\d+: '));
end;
apex_exec.close(l_dml);
rollback;
end;
/Output:
read: 20% - checksum g-9TZ0ojXoLe... update: Row 1: Current version of data in database has changed since user initiated update process.
COPY_DATA
Copies all rows of a query context into a DML context as inserts, or, with p_operation_column_name, with the operation (I, U, or D) that a column of the query holds. Columns are matched by position.
Syntax:
apex_exec.copy_data(p_from_context in t_context, p_to_context in t_context, p_operation_column_name in varchar2 default null)
This example needs a session of application 200, page 1, the city-geocoding REST Data Source, and a target table.
Create the target table first:
create table lab_city_copy (name varchar2(200), admin1 varchar2(200), population number)
Example:
declare
l_params apex_exec.t_parameters;
l_columns apex_exec.t_columns;
l_from apex_exec.t_context;
l_to apex_exec.t_context;
begin
-- copy rows from a REST Data Source into a local table, column by column in order
apex_exec.add_parameter(l_params, 'name', 'Portland');
apex_exec.add_column(l_columns, 'NAME', apex_exec.c_data_type_varchar2);
apex_exec.add_column(l_columns, 'ADMIN1', apex_exec.c_data_type_varchar2);
apex_exec.add_column(l_columns, 'POPULATION', apex_exec.c_data_type_number);
l_from := apex_exec.open_rest_source_query(p_static_id => 'city-geocoding', p_parameters => l_params,
p_columns => l_columns, p_max_rows => 5);
l_to := apex_exec.open_local_dml_context(p_columns => l_columns,
p_query_type => apex_exec.c_query_type_table,
p_table_name => 'LAB_CITY_COPY');
apex_exec.copy_data(p_from_context => l_from, p_to_context => l_to); -- inserts every row
apex_exec.close(l_from);
apex_exec.close(l_to);
end;
/
select name, admin1, population from lab_city_copy order by population desc nulls last;Output:
NAME ADMIN1 POPULATION ----------- -------- ---------- Portland Oregon 652503 Portland Maine 66881 Blue Island Illinois 23652 Portland Texas 16116 Portland Indiana 6186
Five rows from a REST API landed in a local table in one call. For scheduled copies, APEX_REST_SOURCE_SYNC does the same job with change detection.
Duality Views
A query on a Duality View source returns one row per document. p_array_column_name returns a row per element of an array instead, with the document's columns repeated. Filter with ADD_FILTER, because in 26.1 p_where_clause is ignored for Duality Views. A DML context on the source changes whole documents, and its array columns are ignored.
This example needs a session of application 200, page 1, and the order-documents Duality View source. It rolls back at the end.
Example:
declare
l_filters apex_exec.t_filters;
l_columns apex_exec.t_columns;
l_context apex_exec.t_context;
l_status varchar2(30);
begin
-- the lab's Duality View source "order-documents" (ORB_ORDER_DV); p_array_column_name
-- returns a row per element of the LINES array, with the order's columns repeated
apex_exec.add_filter(l_filters, apex_exec.c_filter_in, 'ID', apex_t_number(2, 3));
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_duality_view,
p_duality_view_static_id => 'order-documents',
p_filters => l_filters,
p_array_column_name => 'LINES');
while apex_exec.next_row(l_context) loop
dbms_output.put_line(apex_exec.get_varchar2(l_context, 'ORDERNUMBER') || ' '
|| rpad(apex_exec.get_varchar2(l_context, 'STATUS'), 10) || ' line '
|| apex_exec.get_number(l_context, 'LINENO') || ': product '
|| apex_exec.get_number(l_context, 'PRODUCTID') || ' x '
|| apex_exec.get_number(l_context, 'QUANTITY'));
end loop;
apex_exec.close(l_context);
-- change a document through the duality view
apex_exec.add_column(l_columns, 'ID', apex_exec.c_data_type_number, p_is_primary_key => true);
apex_exec.add_column(l_columns, 'STATUS', apex_exec.c_data_type_varchar2);
l_context := apex_exec.open_duality_view_dml_context(p_static_id => 'order-documents',
p_columns => l_columns);
apex_exec.add_dml_row(l_context, apex_exec.c_dml_operation_update);
apex_exec.set_value(l_context, 'ID', 2);
apex_exec.set_value(l_context, 'STATUS', 'SHIPPED');
apex_exec.execute_dml(l_context);
apex_exec.close(l_context);
select json_value(data, '$.status') into l_status from orb_order_dv where json_value(data, '$._id') = 2;
dbms_output.put_line('ORD-10002 now: ' || l_status);
rollback;
end;
/Output:
ORD-10002 DELIVERED line 1: product 72 x 1 ORD-10003 DELIVERED line 1: product 41 x 1 ORD-10003 DELIVERED line 2: product 6 x 4 ORD-10003 DELIVERED line 3: product 89 x 2 ORD-10003 DELIVERED line 4: product 1 x 1 ORD-10002 now: SHIPPED
The p_where_clause behavior is the second 26.1 surprise: a filter written there is silently ignored for Duality Views, so always use ADD_FILTER with them.
ADD_DML_ARRAY_ROW and GET_ARRAY_ROW_DML_OPERATION
For a REST Data Source whose documents contain arrays, such as an order with its lines, describe the array's columns with p_parent_column_path, open the array of the current row with OPEN_ARRAY, and add elements with ADD_DML_ARRAY_ROW, which makes the new element current for SET_VALUE. GET_ARRAY_ROW_DML_OPERATION returns an element's operation, for REST Data Source plug-ins. Local, remote, and Duality View DML ignore array columns.
Remote Databases and Caches
These need a REST Enabled SQL service, created under Workspace Utilities, Remote Servers; pass its static ID.
| Subprogram | Purpose |
|---|---|
| OPEN_REMOTE_SQL_QUERY(p_server_static_id, p_sql_query, p_sql_parameters, p_auto_bind_items, p_columns, p_first_row, p_max_rows, p_total_row_count, p_total_row_count_limit) | Runs a query on the remote database. |
| EXECUTE_REMOTE_PLSQL(p_server_static_id, p_plsql_code, p_auto_bind_items, p_sql_parameters) | Runs a PL/SQL block there, with binds as in EXECUTE_PLSQL. |
| IS_REMOTE_SQL_AUTH_VALID(p_server_static_id) | Whether the service's credentials work. |
| PURGE_DUALITY_VIEW_CACHE(p_static_id, p_current_session_only), PURGE_JSON_SOURCE_CACHE(...) | Empty the cache of a remote Duality View or JSON source. |
OPEN_WEB_SOURCE_QUERY, OPEN_WEB_SOURCE_DML_CONTEXT, EXECUTE_WEB_SOURCE, and PURGE_WEB_SOURCE_CACHE are deprecated names of the REST_SOURCE subprograms, from when REST Data Sources were called Web Sources.
Conclusion
APEX_EXEC gives PL/SQL one API for every data source an APEX application knows. OPEN_QUERY_CONTEXT with ADD_FILTER and ADD_ORDER_BY runs queries on local tables, remote databases, REST Data Sources, Duality Views, and JSON sources; NEXT_ROW and the GET functions read the rows; and CLOSE, in every exit path, releases the cursor. DML contexts insert, update, and delete with per-row error reporting and checksum-based lost-update detection, and COPY_DATA moves rows between sources in one call. In 26.1, read EXECUTE_PLSQL's OUT parameters as strings, and filter Duality Views with ADD_FILTER rather than p_where_clause.
