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)
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.
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.
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.