How to Commit Just one Data Block changes in Oracle Forms

You have a Oracle Form in which that has multiple data blocks and requirement is-commit just one data block change s and not to effect any other data blocks. But suppose you has a Commit_form button also in form which would commit all the data block changes and that functionality is OK and it should be there. But for a specific block there was a requirement to commits only the block changes when edited.

If you got the kind of requirement then the can insert and update records from the 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, 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 are the screen shot of this form:

you can download this form with the following button: download

As can see in above picture, there is blocks, first one is Department and the second one is Employees and the Re is a push button labeled Commit Employees. In this form if user would change the data of both data blocks and presses the Commit Employees button then it'll save on Ly the Employees data block changes.

Following is the code was 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 would do is, it would check if record status is changed or new and then it'll check from database That the record exists or not and if exists then it'll update else would insert a new record.

How to Commit Just one Data Block changes in Oracle Forms