OPEN_REST_SOURCE_DML_CONTEXT Function

This function opens a DML context based for a REST Data Source.

Syntax

FUNCTION OPEN_REST_SOURCE_DML_CONTEXT (
    p_static_id             IN VARCHAR2,
    p_parameters            IN t_parameters            DEFAULT c_empty_parameters,
    --
    p_columns               IN t_columns               DEFAULT c_empty_columns,
    p_lost_update_detection IN t_lost_update_detection DEFAULT NULL )
    --
    p_fetch_rows_parameters IN t_parameters            DEFAULT c_empty_parameters,
    p_fetch_row_parameters  IN t_parameters            DEFAULT c_empty_parameters,
    p_insert_row_parameters IN t_parameters            DEFAULT c_empty_parameters,
    p_update_row_parameters IN t_parameters            DEFAULT c_empty_parameters,
    p_delete_row_parameters IN t_parameters            DEFAULT c_empty_parameters,
    --
    p_array_column_name     IN VARCHAR2                DEFAULT NULL )
    RETURN t_context;

Parameters

Parameter	Description
`p_static_id`	Static ID of the REST Data Source to use. This REST Data Source must have operations for at least one of the Insert Rows, Update Rows or Delete rows database actions.
`p_parameters`	REST Data Source parameter values to pass to the DML context.
`p_columns`	DML columns to pass to the data source.
`p_lost_update_detection`	Lost-update detection type. Possible values are: `c_lost_update_implicit`: APEX calculates a checksum from the row values. `c_lost_update_explicit`: One of the p_columns has the `is_checksum` attribute set. `c_lost_update_none`: No lost update detection.
`p_fetch_rows_parameters`	REST Data Source parameter values to use only for the "Fetch Rows" operation within this DML context.
`p_fetch_row_parameters`	REST Data Source parameter values to use only for the "Fetch Single Row" operation within this DML context.
`p_insert_row_parameters`	REST Data Source parameter values to use only for the "Update" operation within this DML context.
`p_update_row_parameters`	REST Data Source parameter values to use only for the "Insert" operation within this DML context.
`p_delete_row_parameters`	REST Data Source parameter values to use only for the "Delete" operation within this DML context.
`p_array_column_name`	Name of an array column within the REST Source data profile.

Returns

The context object representing the DML handle.

Example

The following inserts one row into the EMP REST Data Source.

DECLARE
    l_columns        apex_exec.t_columns;
    l_context        apex_exec.t_context;
BEGIN
    -- I. Define DML columns
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'EMPNO',
        p_data_type      => apex_exec.c_data_type_number,
        p_is_primary_key => true );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'ENAME',
        p_data_type      => apex_exec.c_data_type_varchar2 );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'JOB',
        p_data_type      => apex_exec.c_data_type_varchar2 );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'HIREDATE',
        p_data_type      => apex_exec.c_data_type_date );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'MGR',
        p_data_type      => apex_exec.c_data_type_number );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'SAL',
        p_data_type      => apex_exec.c_data_type_number );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'COMM',
        p_data_type      => apex_exec.c_data_type_number );
    apex_exec.add_column(
        p_columns        => l_columns,
        p_column_name    => 'DEPTNO',
        p_data_type      => apex_exec.c_data_type_number );
    
    -- II. Open the context object
    l_context := apex_exec.open_web_source_dml_context(
        p_server_static_id      => '{module static id}',
        p_columns               => l_columns,
        p_lost_update_detection => apex_exec.c_lost_update_none );
    
    -- III. Provide DML data
        
    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_position => 1,
       p_value           => 4711 );
    apex_exec.set_value(
       p_context         => l_context,
       p_column_position => 2,
       p_value           => 'DOE' );
    apex_exec.set_value(
       p_context         => l_context,
       p_column_position => 3,
       p_value           => 'DEVELOPR' );
    apex_exec.set_value(
       p_context         => l_context,
       p_column_position => 4,
       p_value           => sysdate );
    apex_exec.set_value(
       p_context         => l_context,
       p_column_position => 6,
       p_value           => 1000 );
    apex_exec.set_value(
       p_context         => l_context,
       p_column_position => 8,
       p_value           => 10 );
    
    -- IV: 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;