ADD_DML_ARRAY_ROW Procedure

This procedure adds a child row for the current array or the array column provided as p_column_name. The cursor moves to the new row within the specified array column, and all subsequent calls to SET_VALUE target the attributes of this new array element. Only supported within DML contexts on REST Data Sources.

Hierarchical structures are currently only supported for DML on REST Data Sources, if the REST Source type or Plug-In can deal with such structures. DML on a local table or based on REST-Enabled SQL ignores array columns.

The provided array column must be a direct child of the current array column; path syntax and jumping to another position in the hierarchy is unsupported.

Syntax

APEX_EXEC.ADD_DML_ARRAY_ROW (
    p_context               IN t_context,
    p_column_name           IN VARCHAR2        DEFAULT NULL,
    p_column_position       IN PLS_INTEGER,
    p_operation             IN t_dml_operation DEFAULT NULL )

Parameters

Parameter	Description
`p_context`	Context object obtained with one of the `OPEN_` functions.
`p_column_name`	Name of the array column (must exist within the current context) to add a new row for.
`p_column_position`	Position of the column to set the value for within the DML context.
`p_operation`	DML operation to be executed on this row. Use constants `c_dml_operation_*`. If omitted, the child row inherits the operation from its parent.

Example

declare
    l_columns apex_exec.t_columns;
    l_context apex_exec.t_context;

begin

    --
    -- I. Define DML columns    
    --
    -- 1. row-level columns
    --
    apex_exec.add_column( 
        p_columns      => l_columns,
        p_column_name  => 'CUSTOMER_NAME',
        p_data_type    => apex_exec.c_data_type_varchar2 );

    apex_exec.add_column( 
        p_columns      => l_columns,
        p_column_name  => 'ORDER_DATE',
        p_data_type    => apex_exec.c_data_type_date );

    apex_exec.add_column( 
        p_columns      => l_columns,
        p_column_name  => 'ORDER_ITEMS',
        p_data_type    => apex_exec.c_data_type_array );

    --
    -- 2. child columns of the ORDER_ITEMS array column
    --
    apex_exec.add_column( 
        p_columns            => l_columns,
        p_column_name        => 'PRODUCT_ID',
        p_data_type          => apex_exec.c_data_type_number,
        p_parent_column_path => 'ORDER_ITEMS' );

    apex_exec.add_column( 
        p_columns            => l_columns,
        p_column_name        => 'PRODUCT_NAME',
        p_data_type          => apex_exec.c_data_type_varchar2,
        p_parent_column_path => 'ORDER_ITEMS' );

    apex_exec.add_column( 
        p_columns            => l_columns,
        p_column_name        => 'UNIT_PRICE',
        p_data_type          => apex_exec.c_data_type_number,
        p_parent_column_path => 'ORDER_ITEMS' );

    apex_exec.add_column( 
        p_columns            => l_columns,
        p_column_name        => 'AMOUNT_ORDERED',
        p_data_type          => apex_exec.c_data_type_number,
        p_parent_column_path => 'ORDER_ITEMS' );

    --
    -- II. Open the context object    
    --
    l_context := apex_exec.open_rest_source_dml_context(
                     p_columns    => l_columns,
                     p_static_id  => '{module static id}' );

    --
    -- III: Provide DML data
    -- 
    -- 1. the first row
    --
    apex_exec.add_dml_row(
        p_context   => l_context,
        p_operation => apex_exec.c_dml_operation_insert );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'CUSTOMER_NAME',
        p_value        => 'John Doe' );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'ORDER_DATE',
        p_value        => date'2024-03-15' );

    -- 
    -- 1.1. the first line item of the first row
    --
    apex_exec.add_dml_array_row(
        p_context      => l_context,
        p_operation    => apex_exec.c_dml_operation_insert,
        p_column_name  => 'ORDER_ITEMS');

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'PRODUCT_ID',
        p_value        => 100 );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'PRODUCT_NAME',
        p_value        => 'Men''s Jeans size L' );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'UNIT_PRICE',
        p_value        => 30.99 );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'AMOUNT_ORDERED',
        p_value        => 10 );

    --
    -- 1.2. the second line item of the first row
    --
    apex_exec.add_dml_array_row(
        p_context      => l_context );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'PRODUCT_ID',
        p_value        => 101 );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'PRODUCT_NAME',
        p_value        => 'Ladies Jeans size S' );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'UNIT_PRICE',
        p_value        => 30.99 );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'AMOUNT_ORDERED',
        p_value        => 10 );

    -- 
    -- 2. the second row
    --
    apex_exec.add_dml_row(
        p_context   => l_context,
        p_operation => apex_exec.c_dml_operation_insert );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'CUSTOMER_NAME',
        p_value        => 'Jane Doe' );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'ORDER_DATE',
        p_value        => date'2024-03-16' );

    -- 
    -- 2.1. the first line item of the second row
    --
    apex_exec.add_dml_array_row(
        p_context      => l_context,
        p_operation    => apex_exec.c_dml_operation_insert,
        p_column_name  => 'ORDER_ITEMS');

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'PRODUCT_ID',
        p_value        => 100 );

    -- :

    apex_exec.add_dml_array_row(
        p_context      => l_context,
        p_operation    => apex_exec.c_dml_operation_insert );

    -- :

    -- IV: Set "cursor" back to the first child in order to change a value

    apex_exec.set_array_current_row(
        p_context         => l_context,
        p_current_row_idx => 1 );

    apex_exec.set_value(
        p_context      => l_context,
        p_column_name  => 'AMOUNT_ORDERED',
        p_value        => 20 );

    -- V: Execute the DML statement

    apex_exec.execute_dml(
        p_context           => l_context,
        p_continue_on_error => false);

    apex_exec.close( l_context );
exception
    when others then
        apex_exec.close( l_context );
        raise;
end;