24.39 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 )
    RETURN t_context;

Parameters

Table 24-34 OPEN_REST_SOURCE_DML_CONTEXT Function 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.

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;