Developers call handlers from triggers to execute all the code necessary to validate an item or to ensure the correct behavior in a particular situation.
Handlers serve to centralize the code so it is easier to read and work with. A typical form has a package for each block, and a package for the form itself. Place code in procedures within these packages and call the procedures (handlers) from the associated triggers. When a handler involves multiple blocks or responds to form-level triggers, place it in the form package.
There are different kinds of procedures for the different kinds of code, such as item handlers, event handlers, and table handlers. Most code resides in these procedures, and other than calls to them, you should keep code in the triggers to a minimum.
Item handlers are procedures that contain all the logic used for validating a particular item. An item handler package contains all the procedures for validating the items in a block or form.
The packages are usually named after their block or form, while the procedures are named after their particular item. For example, the block EMP includes the items EMPNO, ENAME, and JOB. The corresponding package EMP contains procedures named EMPNO, ENAME, and JOB, making it easy to locate the code associated with a particular item.
An item handler always takes one parameter named EVENT, type VARCHAR2, which is usually the name of the trigger calling the item handler.
The common event points and associated logic are:
| PRE-RECORD | Reset item attributes for the new record. Typically used for APPCORE routines that enable and disable dependent fields. You can use WHEN-NEW-RECORD-INSTANCE for some cases where you need to use restricted Oracle Forms built-in routines or perform navigation or commits. |
| INIT | Initialize the item. |
| VALIDATE | Validate the item and set dynamic item attributes. |
INIT is short for "Initialize" and is a directive to the item handler to initialize the item. INIT tells the item handler to examine current conditions in the form and reset its item's default value and dynamic attributes as necessary. This event is passed by other handlers and is expected by many APPCORE routines.
The most common case is when an item depends on another item. Whenever the master item changes - in WHEN-VALIDATE-ITEM in the master's item handler - the dependent's item handler is called with the INIT event.
When a condition for a master item changes, you typically must cascade the event INIT down to other dependent items.
This pseudo-event is used with many APPCORE routines where the item should be validated. Use this event instead of WHEN-VALIDATE-ITEM, WHEN-CHECKBOX- CHANGED, WHEN-LIST-CHANGED, or WHEN-RADIO- CHANGED (any of which could also be used). You can write your own item handler routines to expect either the VALIDATE event or the trigger names.
A typical item handler looks like this:
procedure ITEM_NAME(event VARCHAR2) IS
IF (event = 'WHEN-VALIDATE-ITEM') THEN
-- validate the item
ELSIF (event = 'INIT') THEN
-- initialize this dependent item
ELSIF (event in ('PRE-RECORD', 'POST-QUERY')) THEN
-- etc.
ELSE fnd_message.debug('Invalid event passed to item_name: ' || EVENT);
END IF;
END ITEM_NAME;
Suggestion: Remember that writing an item handler is not the whole process; you also must code a trigger for each event that the procedure handles and call the item handler. If what you coded is not happening, the first thing to check is whether you coded the trigger to call your new item handler.
Event handlers encapsulate logic that pertains to multiple items where it is easier to centralize the code around an event rather than around individual item behavior. You, the developer, determine when an event handler is easier to read than a set of item handlers.
Very complex cross-item behaviors belong in the event handler, while very simple single item behaviors belong in the item handlers. You can call item handlers from event handlers.
For example, you may code an event handler to populate many items on POST-QUERY. Rather than writing item handlers for each of the items, you could encapsulate all of the logic in a single event handler.
Since an event handler handles only one event, it does not need an EVENT parameter. In fact, it should not take any parameters.
Event handlers are named after the triggers, replacing dashes with underscores (for example, the PRE-QUERY event handler is PRE_QUERY).
| PRE_QUERY | Populates items with values needed to retrieve the appropriate records. |
| POST_QUERY | Populates non-base table items. |
| WHEN_CREATE _RECORD | Populates default values (when using the default value property is insufficient) |
| WHEN_ VALIDATE_ RECORD | Validates complex inter-item relationships |
A table handler is a server-side or client-side package that provides an API to a table. Table handlers are used to insert, update, delete, or lock a record, or to check if a record in another table references a record in this table.
Since most of the forms in Oracle E-Business Suite are based on views, these table handlers are necessary to handle interactions with the tables underneath the views.
Warning: Change the block Key Mode from the default value "Unique Key" to "Non-Updatable Key" when the block is based on a multi-table view. Specify your primary key items by setting "Primary Key" to True in the items' property sheets.
Table handlers contain some or all of the following procedures:
| CHECK_ UNIQUE | Check for duplicate values on unique columns. |
| CHECK_ REFERENCES | Check for referential integrity |
| INSERT_ROW | Insert a row in the table |
| UPDATE_ROW | Update a row in the table |
| DELETE_ROW | Delete a row from the table |
| LOCK_ROW | Lock a row in the table |
INSERT_ROW, UPDATE_ROW, DELETE_ROW, and LOCK_ROW are commonly used to replace default Oracle Forms transaction processing in the ON-INSERT, ON-UPDATE, ON-DELETE, and ON-LOCK triggers.
In the INSERT_ROW table handler procedure, if a primary key column is allowed to be NULL, remember to add "OR (primary_key IS NULL AND X_col IS NULL)" to the SELECT ROWID statement's WHERE clause.
In the LOCK_ROW table handler procedure, if a column is not allowed to be NULL, remove the "OR (RECINFO.col IS NULL AND X_col IS NULL)" condition from the IF statement.
Also, since Oracle Forms strips trailing spaces from queried field values, normal row locking strips trailing spaces from the database values before comparison. Since the example LOCK_ROW stored procedure does not strip trailing spaces, comparison for this (rare) case always fails. You may use RTRIM to strip trailing spaces if necessary.
To perform an action on another table, call that table's appropriate handler procedure rather than performing the action directly.
For example, to perform a cascade DELETE, call the detail table's DELETE_ROWS table handler (which accepts the master primary key as a parameter) instead of performing the DELETE directly in the master table's DELETE_ROW table handler.
The following is an example of a client-side table handler that provides INSERT_ROW, UPDATE_ROW, DELETE_ROW, and LOCK_ROW procedures for the EMP table. You code the client-side table handler directly into your form.
PACKAGE EMP IS PROCEDURE Insert_Row; PROCEDURE Lock_Row; PROCEDURE Update_Row; PROCEDURE Delete_Row; END EMP;
PACKAGE BODY EMP IS
PROCEDURE Insert_Row IS
CURSOR C IS SELECT rowid FROM EMP
WHERE empno = :EMP.Empno;
BEGIN
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
);
OPEN C;
FETCH C INTO :EMP.Row_Id;
if (C%NOTFOUND) then
CLOSE C;
Raise NO_DATA_FOUND;
end if;
CLOSE C;
END Insert_Row;
PROCEDURE Lock_Row IS
Counter NUMBER;
CURSOR C IS
SELECT empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno
FROM EMP
WHERE rowid = :EMP.Row_Id
FOR UPDATE of Empno NOWAIT;
Recinfo C%ROWTYPE;
BEGIN
Counter := 0;
LOOP
BEGIN
Counter := Counter + 1;
OPEN C;
FETCH C INTO Recinfo;
if (C%NOTFOUND) then
CLOSE C;
FND_MESSAGE.Set_Name('FND',
'FORM_RECORD_DELETED');
FND_MESSAGE.Error;
Raise FORM_TRIGGER_FAILURE;
end if;
CLOSE C;
if (
(Recinfo.empno = :EMP.Empno)
AND ( (Recinfo.ename = :EMP.Ename)
OR ( (Recinfo.ename IS NULL)
AND (:EMP.Ename IS NULL)))
AND ( (Recinfo.job = :EMP.Job)
OR ( (Recinfo.job IS NULL)
AND (:EMP.Job IS NULL)))
AND ( (Recinfo.mgr = :EMP.Mgr)
OR ( (Recinfo.mgr IS NULL)
AND (:EMP.Mgr IS NULL)))
AND ( (Recinfo.hiredate = :EMP.Hiredate)
OR ( (Recinfo.hiredate IS NULL)
AND (:EMP.Hiredate IS NULL)))
AND ( (Recinfo.sal = :EMP.Sal)
OR ( (Recinfo.sal IS NULL)
AND (:EMP.Sal IS NULL)))
AND ( (Recinfo.comm = :EMP.Comm)
OR ( (Recinfo.comm IS NULL)
AND (:EMP.Comm IS NULL)))
AND (Recinfo.deptno = :EMP.Deptno)
) then
return;
else
FND_MESSAGE.Set_Name('FND',
'FORM_RECORD_CHANGED');
FND_MESSAGE.Error;
Raise FORM_TRIGGER_FAILURE;
end if;
EXCEPTION
When APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION then
IF (C% ISOPEN) THEN
close C;
END IF;
APP_EXCEPTION.Record_Lock_Error(Counter);
END;
end LOOP;
END Lock_Row;
PROCEDURE Update_Row IS
BEGIN
UPDATE EMP
SET
empno = :EMP.Empno,
ename = :EMP.Ename,
job = :EMP.Job,
mgr = :EMP.Mgr,
hiredate = :EMP.Hiredate,
sal = :EMP.Sal,
comm = :EMP.Comm,
deptno = :EMP.Deptno
WHERE rowid = :EMP.Row_Id;
if (SQL%NOTFOUND) then
Raise NO_DATA_FOUND;
end if;
END Update_Row;
PROCEDURE Delete_Row IS
BEGIN
DELETE FROM EMP
WHERE rowid = :EMP.Row_Id;
if (SQL%NOTFOUND) then
Raise NO_DATA_FOUND;
end if;
END Delete_Row;
END EMP;
The following is an example of a server-side table handler that provides INSERT_ROW, UPDATE_ROW, DELETE_ROW, and LOCK_ROW procedures for the EMP table. Your handler consists of a package in your form and a server-side package in the database. The package in your form calls the server-side package and passes all of the field values as arguments.
PACKAGE EMP IS PROCEDURE Insert_Row; PROCEDURE Update_Row; PROCEDURE Lock_Row; PROCEDURE Delete_Row; END EMP;
PACKAGE BODY EMP IS
PROCEDURE Insert_Row IS
BEGIN
EMP_PKG.Insert_Row(
X_Rowid => :EMP.Row_Id,
X_Empno => :EMP.Empno,
X_Ename => :EMP.Ename,
X_Job => :EMP.Job,
X_Mgr => :EMP.Mgr,
X_Hiredate => :EMP.Hiredate,
X_Sal => :EMP.Sal,
X_Comm => :EMP.Comm,
X_Deptno => :EMP.Deptno);
END Insert_Row;
PROCEDURE Update_Row IS
BEGIN
EMP_PKG.Update_Row(
X_Rowid => :EMP.Row_Id,
X_Empno => :EMP.Empno,
X_Ename => :EMP.Ename,
X_Job => :EMP.Job,
X_Mgr => :EMP.Mgr,
X_Hiredate => :EMP.Hiredate,
X_Sal => :EMP.Sal,
X_Comm => :EMP.Comm,
X_Deptno => :EMP.Deptno);
END Update_Row;
PROCEDURE Delete_Row IS BEGIN EMP_PKG.Delete_Row(:EMP.Row_Id); END Delete_Row;
PROCEDURE Lock_Row IS
Counter Number;
BEGIN
Counter := 0;
LOOP
BEGIN
Counter := Counter + 1;
EMP_PKG.Lock_Row(
X_Rowid => :EMP.Row_Id,
X_Empno => :EMP.Empno,
X_Ename => :EMP.Ename,
X_Job => :EMP.Job,
X_Mgr => :EMP.Mgr,
X_Hiredate => :EMP.Hiredate,
X_Sal => :EMP.Sal,
X_Comm => :EMP.Comm,
X_Deptno => :EMP.Deptno);
return;
EXCEPTION
When APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION then
APP_EXCEPTION.Record_Lock_Error(Counter);
END;
end LOOP;
END Lock_Row;
END EMP;
SET VERIFY OFF
DEFINE PACKAGE_NAME="EMP_PKG"
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
CREATE or REPLACE PACKAGE &PACKAGE_NAME as
/* Put any header information (such as $Header: DEV00009170.htm 120.5 2010/05/20 19:38:09 appldev ship $) here.
It must be written within the package definition so that the
header information will be available in the package itself.
This makes it easier to identify package versions during
upgrades. */
PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
X_Empno NUMBER,
X_Ename VARCHAR2,
X_Job VARCHAR2,
X_Mgr NUMBER,
X_Hiredate DATE,
X_Sal NUMBER,
X_Comm NUMBER,
X_Deptno NUMBER
);
PROCEDURE Lock_Row(X_Rowid VARCHAR2,
X_Empno NUMBER,
X_Ename VARCHAR2,
X_Job VARCHAR2,
X_Mgr NUMBER,
X_Hiredate DATE,
X_Sal NUMBER,
X_Comm NUMBER,
X_Deptno NUMBER
);
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Empno NUMBER,
X_Ename VARCHAR2,
X_Job VARCHAR2,
X_Mgr NUMBER,
X_Hiredate DATE,
X_Sal NUMBER,
X_Comm NUMBER,
X_Deptno NUMBER
);
PROCEDURE Delete_Row(X_Rowid VARCHAR2);
END &PACKAGE_NAME; / show errors package &PACKAGE_NAME
SELECT to_date('SQLERROR') FROM user_errors
WHERE name = '&PACKAGE_NAME'
AND type = 'PACKAGE'
/
commit;
exit;
SET VERIFY OFF DEFINE PACKAGE_NAME="EMP_PKG" WHENEVER SQLERROR EXIT FAILURE ROLLBACK; CREATE or REPLACE PACKAGE BODY &PACKAGE_NAME as /* Put any header information (such as $Header: DEV00009170.htm 120.5 2010/05/20 19:38:09 appldev ship $) here. It must be written within the package definition so the header information is available in the package itself. This makes it easier to identify package versions during upgrades. */
PROCEDURE Insert_Row(X_Rowid IN OUT VARCHAR2,
X_Empno NUMBER,
X_Ename VARCHAR2,
X_Job VARCHAR2,
X_Mgr NUMBER,
X_Hiredate DATE,
X_Sal NUMBER,
X_Comm NUMBER,
X_Deptno NUMBER
) IS
CURSOR C IS SELECT rowid FROM emp
WHERE empno = X_Empno;
BEGIN
INSERT INTO emp(
empno,
ename,
job,
mgr,
hiredate,
sal,
comm,
deptno
) VALUES (
X_Empno,
X_Ename,
X_Job,
X_Mgr,
X_Hiredate,
X_Sal,
X_Comm,
X_Deptno
);
OPEN C;
FETCH C INTO X_Rowid;
if (C%NOTFOUND) then
CLOSE C;
Raise NO_DATA_FOUND;
end if;
CLOSE C;
END Insert_Row;
PROCEDURE Lock_Row(X_Rowid VARCHAR2,
X_Empno NUMBER,
X_Ename VARCHAR2,
X_Job VARCHAR2,
X_Mgr NUMBER,
X_Hiredate DATE,
X_Sal NUMBER,
X_Comm NUMBER,
X_Deptno NUMBER
) IS
CURSOR C IS
SELECT *
FROM emp
WHERE rowid = X_Rowid
FOR UPDATE of Empno NOWAIT;
Recinfo C%ROWTYPE;
BEGIN
OPEN C;
FETCH C INTO Recinfo;
if (C%NOTFOUND) then
CLOSE C;
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
APP_EXCEPTION.Raise_Exception;
end if;
CLOSE C;
if (
(Recinfo.empno = X_Empno)
AND ( (Recinfo.ename = X_Ename)
OR ( (Recinfo.ename IS NULL)
AND (X_Ename IS NULL)))
AND ( (Recinfo.job = X_Job)
OR ( (Recinfo.job IS NULL)
AND (X_Job IS NULL)))
AND ( (Recinfo.mgr = X_Mgr)
OR ( (Recinfo.mgr IS NULL)
AND (X_Mgr IS NULL)))
AND ( (Recinfo.hiredate = X_Hiredate)
OR ( (Recinfo.hiredate IS NULL)
AND (X_Hiredate IS NULL)))
AND ( (Recinfo.sal = X_Sal)
OR ( (Recinfo.sal IS NULL)
AND (X_Sal IS NULL)))
AND ( (Recinfo.comm = X_Comm)
OR ( (Recinfo.comm IS NULL)
AND (X_Comm IS NULL)))
AND (Recinfo.deptno = X_Deptno)
) then
return;
else
FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
APP_EXCEPTION.Raise_Exception;
end if;
END Lock_Row;
PROCEDURE Update_Row(X_Rowid VARCHAR2,
X_Empno NUMBER,
X_Ename VARCHAR2,
X_Job VARCHAR2,
X_Mgr NUMBER,
X_Hiredate DATE,
X_Sal NUMBER,
X_Comm NUMBER,
X_Deptno NUMBER
) IS
BEGIN
UPDATE emp
SET
empno = X_Empno,
ename = X_Ename,
job = X_Job,
mgr = X_Mgr,
hiredate = X_Hiredate,
sal = X_Sal,
comm = X_Comm,
deptno = X_Deptno
WHERE rowid = X_Rowid;
if (SQL%NOTFOUND) then
Raise NO_DATA_FOUND;
end if;
END Update_Row;
PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
BEGIN
DELETE FROM emp
WHERE rowid = X_Rowid;
if (SQL%NOTFOUND) then
Raise NO_DATA_FOUND;
end if;
END Delete_Row;
END &PACKAGE_NAME; / show errors package body &PACKAGE_NAME
SELECT to_date('SQLERROR') FROM user_errors
WHERE name = '&PACKAGE_NAME'
AND type = 'PACKAGE BODY'
/
commit;
exit;