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:
You can download this form with the following button: Download
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:
DECLARE
CURSOR c_emp (p_emp emp.empno%TYPE)
IS
SELECT 'Y'
FROM emp
WHERE emp.empno = p_emp;
v_exists VARCHAR2 (1);
BEGIN
GO_BLOCK ('Emp');
FIRST_RECORD;
LOOP
IF :SYSTEM.record_status = 'CHANGED'
OR:SYSTEM.record_status = 'INSERT'
THEN
OPEN c_emp (:emp.empno);
FETCH c_emp INTO v_exists;
CLOSE c_emp;
IF NVL (v_exists, 'N') = 'Y'
THEN
UPDATE emp
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;
ELSE
INSERT INTO emp (empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno)
VALUES (:emp.empno,
:emp.ename,
:emp.job,
:emp.mgr,
:emp.hiredate,
:emp.sal,
:emp.comm,
:emp.deptno);
END IF;
END IF;
IF :SYSTEM.LAST_RECORD = 'TRUE'
THEN
EXIT;
END IF;
NEXT_RECORD;
END LOOP;
FORMS_DDL ('commit');
-- REQUERY TO REFRESH CHANGES
CLEAR_BLOCK (no_validate);
GO_BLOCK ('dept');
CLEAR_BLOCK (no_validate);
EXECUTE_QUERY;
EXCEPTION
WHEN OTHERS
THEN
FORMS_DDL ('rollback');
MESSAGE ('error occurred.');
END;
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.
