You have an Oracle Form in which you have multiple data blocks and requirement is to commit just one data block changes and not to effect any other data blocks. But suppose you have a commit_form button also in form which will commit all the data block changes and that functionality is ok and it should be there. But for a specific block there is a requirement to commit only that block changes when edited.
If you got this kind of requirement then you can insert and update records from that data block to database externally, I mean using insert and update statements and not by Oracle form’s default commit behavior.
To accomplish this task you need to give a push button to the user to save explicitly that data block changes. I have created a form for this example and below is the screen shot of this form:
As you can see in above picture, there are two blocks, first one is Department and the second one is Employees and there is a push button labeled Commit Employees. In this form if user will change the data in both data blocks and presses the Commit Employees button then it will save only the Employees data block changes.
Following is the code is written in Commit Employees button to perform this task:
CURSOR c_emp (p_emp emp.empno%TYPE)
WHERE emp.empno = p_emp;
v_exists VARCHAR2 (1);
IF :SYSTEM.record_status = ‘CHANGED’
OR:SYSTEM.record_status = ‘INSERT’
OPEN c_emp (:emp.empno);
FETCH c_emp INTO v_exists;
IF NVL (v_exists, ‘N’) = ‘Y’
SET ename = :emp.ename,
job = :emp.job,
mgr = :emp.mgr,
hiredate = :emp.hiredate,
sal = :emp.sal,
comm = :emp.comm,
deptno = :emp.deptno
WHERE empno = :emp.empno;
INSERT INTO emp (empno,
IF :SYSTEM.LAST_RECORD = ‘TRUE’
— REQUERY TO REFRESH CHANGES
MESSAGE (‘error occurred.’);
What this above code will do is, it will check if record status is changed or new and then it will check from database that the record exists or not and if exists then it will update else will insert a new record.