18.4.8.11 Writing Business Objects with Nested Collections
Use the APEX_EXEC package to programmatically write a
parent object and its nested child objects in a single payload.
Tip:
For all create, update, and delete use cases to work as expected, the top-level REST Data Source must configured so that Use Bulk DML is set to Yes.Consider the following example:
function quick_create_order_exec(
p_customer_id in number,
p_product_id1 in number,
p_quantity1 in number,
p_product_id2 in number,
p_quantity2 in number)
return number
is
l_ctx apex_exec.t_context;
l_order_id number;
begin
l_ctx := apex_exec.open_rest_source_dml_context(
p_static_id => 'orders_with_details',
p_columns => get_order_create_columns,
p_lost_update_detection => apex_exec.c_lost_update_none);
apex_exec.add_dml_row(l_ctx, apex_exec.c_dml_operation_insert);
apex_exec.set_value(l_ctx, 'CUSTOMERID', p_customer_id);
-- Set the context to the ORDERLINES array
-- and add the first row in the array
apex_exec.add_dml_array_row(l_ctx, 'ORDERLINES');
apex_exec.set_value(l_ctx, 'ORDERLINES_PRODUCTID', p_product_id1);
apex_exec.set_value(l_ctx, 'ORDERLINES_QUANTITY', p_quantity1);
if p_quantity2 is not null
and p_product_id2 is not null
then
-- ORDERLINES array is already current. Add another row to array
apex_exec.add_dml_array_row(l_ctx);
apex_exec.set_value(l_ctx, 'ORDERLINES_PRODUCTID', p_product_id2);
apex_exec.set_value(l_ctx, 'ORDERLINES_QUANTITY', p_quantity2);
end if;
-- Do the DML operation
apex_exec.execute_dml(l_ctx);
-- Retrieve system-assigned order number & return it
apex_exec.set_current_row(l_ctx,1);
l_order_id := apex_exec.get_varchar2(l_ctx,'APEX$RESOURCEKEY');
apex_exec.close(l_ctx);
return l_order_id;
end quick_create_order_exec;The previous code example above references a
get_order_create_columns(). The following example returns the
apex_exec.t_columns table of
apex_exec.t_column records defining the columns participating
in the DML operation.
function get_order_create_columns
return apex_exec.t_columns
is
l_columns apex_exec.t_columns;
begin
apex_exec.add_column( /* APEX$ResourceKey */
p_columns => l_columns,
p_column_name => 'APEX$RESOURCEKEY',
p_data_type => apex_exec.c_data_type_varchar2,
p_is_primary_key => true);
apex_exec.add_column( /* customerId */
p_columns => l_columns,
p_column_name => 'CUSTOMERID',
p_data_type => apex_exec.c_data_type_number);
apex_exec.add_column( /* orderLines.items */
p_columns => l_columns,
p_column_name => 'ORDERLINES',
p_data_type => apex_exec.c_data_type_array);
apex_exec.add_column( /* orderLines.productId */
p_columns => l_columns,
p_column_name => 'ORDERLINES_PRODUCTID',
p_data_type => apex_exec.c_data_type_number,
p_parent_column_path => '"ORDERLINES"');
apex_exec.add_column( /* orderLines.quantity */
p_columns => l_columns,
p_column_name => 'ORDERLINES_QUANTITY',
p_data_type => apex_exec.c_data_type_number,
p_parent_column_path => '"ORDERLINES"');
return l_columns;
end get_order_create_columns;