15.40 SET_ROW_VERSION_CHECKSUM Procedure

This procedure sets the row version checksum to use for lost update detection for the current DML row. This is called after add_dml_row.

Syntax

PROCEDURE SET_ROW_VERSION_CHECKSUM(
    p_context               IN t_context,
    p_checksum              IN VARCHAR2 );

Parameters

Table 15-38 SET_ROW_VERSION_CHECKSUM Procedure Parameters

Parameter	Description
p_context	Context object obtained with one of the OPEN_ functions.
p_checksum	checksum to use for lost-update detection of this row.

Example

The following example opens a query context on the EMP table and retrieves all values and the row version checksum for the row with EMPNO=7839. Then a DML context is opened to update the SAL column while using the row version checksum for lost update detection.

declare
    l_columns        apex_exec.t_columns;
    l_dml_context    apex_exec.t_context;
    l_query_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 Query Context object
    l_query_context := apex_exec.open_remote_sql_query(
        p_server_static_id  => 'DevOps_Remote_SQL',
        p_sql_query         => 'select * from emp where empno = 7839',
        p_columns           => l_columns );
    
     -- III. Open the DML context object
     l_dml_context := apex_exec.open_remote_dml_context(
         p_server_static_id      => '{remote server static id}',
         p_columns               => l_columns,
         p_query_type            => apex_exec.c_query_type_sql_query,
         p_sql_query             => 'select * from emp where deptno = 10',
         p_lost_update_detection => apex_exec.c_lost_update_implicit );
    
     if apex_exec.next_row( p_context => l_query_context ) then
         apex_exec.add_dml_row(
             p_context   => l_dml_context,
             p_operation => apex_exec.c_dml_operation_update);

      apex_exec.set_row_version_checksum(
         p_context   => l_dml_context,
         p_checksum  => apex_exec.get_row_version_checksum( p_context => l_query_context );

      apex_exec.set_values(
         p_context         => l_dml_context,
         p_cource_context  => l_query_context );

      apex_exec.set_value(
         p_column_name => 'SAL',
         p_value       => 8000 );
     else
         raise_application_error( -20000, 'EMPNO #4711 is not present!');
     end if;
        
     apex_exec.execute_dml(
         p_context           => l_dml_context,
         p_continue_on_error => false);
    
     apex_exec.close( l_dml_context );
     apex_exec.close( l_query_context );
exception 
      when others then
           apex_exec.close( l_dml_context );
           apex_exec.close( l_query_context );
           raise;
            
end;