Manual Row Lock for DML Operation in Oracle Apex

In this article we are gonna see that how to prevent or avoid data losses if two user update the same record at same time.

In Oracle Apex, if we use Automatic Row Processing for DML(insert,update,delete) operation then automatically Apex prevent the data losses from the DML operation. But when we do the same manually, Oracle Apex application won't prevent data loses from the update/delete.

But we can also prevent this data loses manually by using GET_HASH Function which is provided by Oracle Apex API. For more info(GET_HASH Function). Let see how to achieve this with one example.

Before that I would like to add what GET_HASH Function is all about:
This function computes a hash value for all given values. Use this function to implement lost update detection for data records.


Let's consider I have two pages, one is normal report page (e.g page 8).

and another one is form page (e.g page 10).

Let's consider I am retrieving and updating the data manually.

In form page, I have created one page item (i.e P10_HASH) for getting the hash value using the below given query:(This code need to write Pre-rendering Section)

select apex_util.get_hash(apex_t_varchar2 (
                  ENAME, sal, JOB ))
         into :P10_hash
         from emp
        where empno = :P10_EMPID;

In Validation section, I am retrieving the value from database and checking whether the retrieved data is latest one. If not raise a validation error otherwise allow for DML operation. Code given below.

declare
       l_hash varchar2(4000);
   begin
       select apex_util.get_hash(apex_t_varchar2 (
                  ename, sal, job ))
         into l_hash
         from emp
        where empno = :P10_EMPID;
 
       if :P10_HASH <> l_hash then
          RETURN 'Somebody already updated SAL/JOB/NAME. Please refresh the page and tryagain.';
       end if;
 
   exception when no_data_found then
      RETURN 'Employee not found';
   end;

Thank you for reading this article. I hope this article gave some idea about how to prevent the data loses manually from the DML operation. If you have any clarification or suggestion, leave it message in the comment section.