Previous Next JavaScript must be enabled to correctly display this content

## 18.5.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;

Parent topic: Working with Oracle Cloud Applications BOSS REST Services