You can use PL/SQL procedures as part of an application that you build around Oracle E-Business Suite. By following the coding standards, you can create a PL/SQL procedure that integrates seamlessly with your application and with Oracle E-Business Suite.
You use PL/SQL to:
Develop procedural extensions to your forms and reports quickly and easily
Modularize your application code to speed development and improve maintainability
Optimize your application code to reduce network traffic and improve overall performance
You can use PL/SQL, Oracle's procedural language extension to SQL, to develop procedural extensions to custom forms and reports you create with Oracle tools.
For example, to develop a form that follows Oracle E-Business Suite standards, you organize your form code into PL/SQL business rule procedures, item handlers, event handlers, and table handlers. You put very little PL/SQL code directly into form triggers because those triggers do not represent a logical model; they are simply event points that Oracle Forms provides for invoking procedural code. If you put most of your code in packaged PL/SQL procedures, and then call those procedures from your triggers, you will have modular form code that is easy to develop and maintain.
You may write any PL/SQL procedure that helps you modularize your form code. For example, an item handler, event handler, or business rule procedure may actually consist of several smaller procedures. Be sure to group these smaller procedures into logical packages so their purpose is clear. (There is no special name for these smaller procedures. They are simply PL/SQL procedures.)
You can also use PL/SQL to develop concurrent programs or stored procedures that are called from concurrent programs. Generally, any concurrent program you would have developed as an immediate concurrent program in past releases of Oracle E-Business Suite could be developed as a PL/SQL concurrent program. Or, you may develop the main body of your concurrent program in C, but encapsulate any SQL statements issued by your concurrent program in PL/SQL stored procedures.
Important: Oracle Application Object Library does not support code using database links. Any code that calls Oracle Application Object Library core PL/SQL APIs will most likely fail if a database link is involved.
Here are definitions of two terms used in this chapter.
Server-side is a term used to describe PL/SQL procedures that are stored in an Oracle database (on the database server). Procedures and functions stored in the database are also referred to as stored procedures and functions, and may also be referred to as being database server-side procedures.
Client-side is a term used to describe PL/SQL procedures that run in programs that are clients of the Oracle database, such as Oracle Forms, Oracle Reports, and libraries.
The term "client-side" in this manual usually refers to the forms server (where the forms reside). "Client-side" in this manual does not typically refer to the "desktop client", which is usually a PC or other desktop machine running a Web browser.
Here are general standards you should follow.
PL/SQL procedures should always be defined within packages. Create a package for each block of a form, or other logical grouping of code.
A client-side (Oracle Forms) PL/SQL program unit's source code and compiled code together must be less than 64K. (A program unit is a package specification or body or stand-alone procedure.) This implies that the source code for a program unit cannot exceed 10K.
If a package exceeds the 10K limit, you can reduce the size of the package by putting private variables and procedures in one or more "private packages." By standard, only the original package should access variables and procedures in a private package. If an individual procedure exceeds the size limit, you should separate the code into two or more procedures.
When an Oracle Forms PL/SQL procedure exceeds the 64K limit, Oracle Forms raises an error at generate time.
Server-side packages and procedures do not have a size limit, but when Oracle Forms refers to a server-side package or procedure, it creates a local stub, which does have a size limit. The size of a package stub depends on the number of procedures in the package and the number and types of arguments each procedure has. Keep the number of procedures in a package less than 25 to avoid exceeding the 10K limit.
When you add new procedures or functions to existing packages (either stored in the database or in Oracle Forms libraries), you should usually add them to the end of the package (and package specification). If you add new procedures to the middle of the package specification and package, you must regenerate every form that references the package, or those forms may get ORA-4062 errors.
Always specify field names completely by including the block name (that is, BLOCK.FIELD_NAME instead of just FIELD_NAME). If you specify just the field name, Oracle Forms must scan through the entire list of fields for each block in the form to locate your field and check if its name is ambiguous, potentially degrading your form performance. If you include the block name, Oracle Forms searches only the fields in that block and stops when it finds a match. Moreover, if you ever add more blocks, your existing code continues to work since you specified your field names unambiguously.
Pass field names to procedures and use COPY to update field values instead of using IN OUT or OUT parameters. This method prevents a field from being marked as changed whether or not you actually modify it in your procedure. Any parameter declared as OUT is always written to when the procedure exits normally.
For example, declare a procedure as test(my_var VARCHAR2 IN) and call it as test('block.field') instead of declaring the procedure as test(my_var VARCHAR2 IN OUT) and calling it as test(:block.field).
Explicitly associate the parameter name and value with => when the parameter list is long to improve readability and ensure that you are not "off" by a parameter.
Use DEFAULT instead of ":=" when declaring default values for your parameters. DEFAULT is more precise because you are defaulting the values; the calling procedure can override the values.
Conversely, use ":=" instead of DEFAULT when declaring values for your constant variables. Using ":=" is more precise because you are assigning the values, not defaulting them; the values cannot be overridden.
Any code that changes multiple properties of an object using the SET_<OBJECT>_PROPERTY built-in (or the Oracle Application Object Library equivalent) should use object IDs. First use the appropriate FIND_<OBJECT> built-in to get the ID, then pass the ID to the SET_<OBJECT>_PROPERTY built-in.
You should also consider storing the ID in a package global so that you retrieve it only once while the form is running.
Use caution when handling NULL values in PL/SQL. For example, if a := NULL and b := NULL, the expression (a = b) evaluates to FALSE. In any "=" expression where one of the terms is NULL, the whole expression will resolve to FALSE.
For this reason, to check if a value is equal to NULL, you must use the operator "is" instead. If you're comparing two values where either of the values could be equal to NULL, you should write the expression like this: ((a = b) or ((a is null) and (b is null))
Oracle Forms Developer and PL/SQL support different types of global variables:
Oracle Forms Global: a variable in the "global" pseudo-block of a form
PL/SQL Package Global: a global defined in the specification of a package
Oracle Forms Parameter: a variable created within the Oracle Forms Designer as a Parameter
See the Oracle Forms Reference Manual for a complete description of these variable types. The following table lists the characteristics of each type of variable, and enables you to select the type most appropriate for your code.
| Behavior | Oracle Forms Global | PL/SQL Package Global | Oracle Forms Parameter |
|---|---|---|---|
| Can be created at Design time | Y | Y | |
| Can be created at runtime | Y | ||
| Accessible across all forms | Y | ||
| Accessible from attached libraries | Y | (1) | Y |
| Support specific datatypes | (2) | Y | Y |
| Have declarative defaults | Y | ||
| Can be referenced indirectly | Y | Y | |
| Can be specified on command line | Y | ||
| Must be erased to recover memory | Y | ||
| Can be used in any Oracle Forms code | Y | Y |
(1) A package variable defined in a form is not visible to any attached library; a variable defined in an attached library is visible to the form. (An Oracle Forms Global is visible to an attached library)
(2) Always CHAR(255).
Performance is a critical aspect of any application. Because network round trips are very costly in a typical client-server environment, minimizing the number of round trips is key to ensuring good performance.
You should decide whether your PL/SQL procedures reside on the server or on the client based on whichever results in the fewest number of network round trips. Here are some guidelines:
Procedures that call Oracle Forms built-ins (more generally, client built-ins) must reside on the client.
Procedures that reference fields directly, either as :block.field or via NAME_IN/COPY, must reside on the client. You can avoid referencing fields directly by accepting field values or names as parameters to your PL/SQL procedures, which also improves your code's modularity.
If a procedure contains three or more SQL statements, or becomes very complicated, the procedure usually belongs on the server.
Procedures that perform no SQL and that need no database access should reside wherever they are needed.
If a procedure is called from the server, it must reside on the server. If a procedure is called from both client and server, it should be defined in both places, unless the procedure is very complicated and double maintenance is too costly. In the latter case, the procedure should reside on the server.
This section contains recommendations for formatting PL/SQL code.
Within a package, define private variables first, then private procedures, and finally public procedures.
Always end procedures and packages by following the "end" statement with the procedure or package name to help delineate procedures.
Indent code logically. Using increments of two spaces provides an easy way to track your nested cases.
Indent SQL statements as follows:
Example
DECLARE
CURSOR employees IS
SELECT empno
FROM emp
WHERE deptno = 10
AND ename IN ('WASHINGTON', 'MONROE')
AND mgr = 2701;Use "- -" to start comments so that you can easily comment out large portions of code during debugging with "/* ... */".
Indent comments to align with the code being commented.
When commenting out code, start the comment delimiter in the leftmost column. When the code is clearly no longer needed, remove it entirely.
Use uppercase and lowercase to improve the readability of your code (PL/SQL is case-insensitive). As a guideline, use uppercase for reserved words and lowercase for everything else.
Avoid deeply nested IF-THEN-ELSE condition control. Use IF-THEN-ELSIF instead.
Example of Bad Style
IF ... THEN ... ELSE
IF ... THEN ... ELSE
IF ... THEN ... ELSE
END IF
END IF
END IF;
Example of Good Style
IF ... THEN ... ELSIF ... THEN ... ELSIF ... THEN ... ELSIF ... THEN ... ELSE ... END IF;
Only create nested PL/SQL blocks (BEGIN/END pairs) within a procedure when there is specific exception handling you need to trap.
For exception handling, use the following tips.
If a failure occurs in Oracle Forms PL/SQL and you want to stop further processing, use FND_MESSAGE to display an error message, then RAISE FORM_TRIGGER_FAILURE to stop processing:
IF (error_condition) THEN
fnd_message.set_name(appl_short_name,
message_name);
fnd_message.error;
RAISE FORM_TRIGGER_FAILURE;
END IF;
Note that RAISE FORM_TRIGGER_FAILURE causes processing to stop quietly. Since there is no error notification, you must display any messages yourself using FND_MESSAGE before raising the exception.
See: Message Dictionary APIs for PL/SQL Procedures
If a failure occurs in a stored procedure and you want to stop further processing, use the package procedures FND_MESSAGE.SET_NAME to set a message, and APP_EXCEPTION.RAISE_EXCEPTION to stop processing:
IF (error_condition) THEN
fnd_message.set_name(appl_short_name,
message_name);
APP_EXCEPTION.RAISE_EXCEPTION;
END IF;
The calling procedure in the form does not need to do anything to handle this stored procedure error. The code in the ON-ERROR trigger of the form automatically detects the stored procedure error and retrieves and displays the message.
Important: For performance reasons, server side packages should return a return_code for all expected returns, such as no_rows. Only unexpected exceptions should be processed with an exception handler.
See: Message Dictionary APIs for PL/SQL Procedures, Special Triggers in the TEMPLATE form, and APP_EXCEPTION: Exception Processing APIs
When testing FORM_SUCCESS, FORM_FAILURE, or FORM_FATAL be aware that their values may be changed by a built-in in another trigger that is fired as a result of your built-in. For example, consider the following code:
GO_ITEM('emp.empno');
IF FORM_FAILURE THEN
RAISE FORM_TRIGGER_FAILURE;
END IF;
The GO_ITEM causes other triggers to fire, such as WHEN-NEW-ITEM-INSTANCE. Although the GO_ITEM may fail, the last trigger to fire may succeed, meaning that FORM_FAILURE is false. The following example avoids this problem.
GO_ITEM('EMP.EMPNO');
IF :SYSTEM.CURSOR_ITEM != 'EMP.EMPNO' THEN
-- No need to show an error, because Oracle Forms
-- must have already reported an error due to
-- some other condition that caused the GO_ITEM
-- to fail.
RAISE FORM_TRIGGER_FAILURE;
END IF;
See the Oracle Forms Reference Manual for other techniques to trap the failure of each built-in.
Do not use RAISE_APPLICATION_ERROR. It conflicts with the scheme used to process server side exceptions.
See: Message Dictionary APIs for PL/SQL Procedures
Follow these guidelines for all SQL that you code:
Use "select from DUAL" instead of "select from SYS.DUAL". Do not use SYSTEM.DUAL.
All SELECT statements should use an explicit cursor. Implicit SELECT statements actually cause 2 fetches to execute: one to get the data, and one to check for the TOO_MANY_ROWS exception. You can avoid this by FETCHing just a single record from an explicit cursor.
If you want to SELECT into a procedure parameter, declare the parameter as IN OUT, whether or not you reference the parameter value, unless the parameter is a field.
A single-row SELECT that returns no rows raises the exception NO_DATA_FOUND. An INSERT, UPDATE, or DELETE that affects no rows does not raise an exception. You need to explicitly check the value of SQL%NOTFOUND if no rows is an error.
To handle NO_DATA_FOUND exceptions, write an exception handler. Do not code COUNT statements to detect the existence of rows unless that is your only concern.
When checking the value of a field or PL/SQL variable against a literal, do the check in PL/SQL code, not in a WHERE clause. You may be able to avoid doing the SQL altogether.
Do not check for errors due to database integrity problems. For example, if a correct database would have a table SYS.DUAL with exactly one row in it, you do not need to check if SYS.DUAL has zero or more than one row or if SYS.DUAL exists.
Follow these general rules for triggers in your forms.
The 'Execution Style' for all block or field level triggers should either be Override or Before. In general, use style Before, since usually the form-level version of the trigger should also fire. The exception is if you have a flexfield call in the form-level POST-QUERY trigger, but you reset the query status of the block in the block level POST-QUERY. In that case, the block-level POST-QUERY should use Execution Style After.
See: Special Triggers in the TEMPLATE form
Set the "Show Keys" property to True for all KEY- triggers you code, except those that you are disabling (which should have "Show Keys" set to False). Always set the "Show Keys Description" property to NULL.
The WHEN-CREATE-RECORD trigger fires even when the block does not allow inserts. You may need to check if the block allows insert if you have logic in this trigger and your block may dynamically have insert-allowed "FALSE":
IF GET_ITEM_PROPERTY('<BLOCK>', INSERT_ALLOWED) = FALSE THEN
null;
ELSE
<your logic here>;
END IF;
On the PC there is a limit to the number of real widgets available simultaneously (text items and display items are not real Windows widgets, as Oracle Forms creates these items). Every check box, list item, and object group in your form consumes these resources.
If a real widget is on a hidden canvas, the resources it consumes are freed. You can free resources by explicitly hiding a canvas that does not appear on the screen. Also, any canvas set with a display property of FALSE in the Oracle Forms Designer does not consume resources for itself or its widgets until the canvas is visited or the canvas is programmatically displayed.
Remember that Oracle Forms navigates to the first enterable item at startup time, which creates the canvas and all its widgets for the First Navigation Block.
To check the availability of MS Windows resources before performing some action, use the following utility:
if get_application_property(USER_INTERFACE) =
'MSWINDOWS' then
if (FND_UTILITIES.RESOURCES_LOW) then
FND_MESSAGE.SET_NAME('FND', 'RESOURCES_LOW');
if (FND_MESSAGE.QUESTION('Do Not Open', 'Open',
'', 1) =1) then
raise FORM_TRIGGER_FAILURE;
end if;
end if;
end if;
These standards require that certain built-ins be avoided entirely, or "wrapper" routines be called in their place. For many built-ins, there are multiple methods of invocation. You can call the built-in directly, giving you the standard forms behavior. For some built-ins, there are standard Oracle E-Business Suite behaviors, which you invoke by calling APP_STANDARD.EVENT.
Many of these built-ins have a key and a KEY- trigger associated with them. If there is any additional logic which has been added to the KEY- trigger that you want to take advantage of, you can invoke the trigger by using the DO_KEY built-in. This is the same result you would get if the user pressed the associated key.
You should routinely use the DO_KEY built-in. The only reason to bypass the KEY- trigger is if you need to avoid the additional code that would fire.
Do not use this Oracle Forms built-in:
| Variable | Description |
|---|---|
| CALL_FORM | This built-in is incompatible with OPEN_FORM, which is used by Oracle E-Business Suite routines. You should use FND_FUNCTION.EXECUTE instead of either CALL_FORM or OPEN_FORM whenever you need to open a form programatically. Using FND_FUNCTION.EXECUTE allows you to open forms without bypassing Oracle E-Business Suite security, and takes care of finding the correct directory path for the form. See: Function Security APIs for PL/SQL Procedures |
These Oracle Forms built-ins have equivalent APPCORE routines that provide additional functionality:
| Variable | Description |
|---|---|
| EXIT_FORM | The Oracle E-Business Suite forms have special exit processing. Do not call EXIT_FORM directly; always call do_key('EXIT_FORM'). To exit the entire Oracle E-Business Suite, first call: copy('Y','GLOBAL.APPCORE_EXIT_FLAG');
Then call: do_key('exit_form'); |
| SET_ITEM_ PROPERTY | Replace with APP_ITEM_PROPERTY.SET_ PROPERTY and APP_ITEM_PROPERTY.SET_ VISUAL_ATTRIBUTE. These APPCORE routines set the properties in the Oracle E-Business Suite standard way and change the propagation behavior. Some properties use the native Oracle Forms SET_ITEM_PROPERTY. For a complete list of properties that APP_ITEM_PROPERTY.SET_ PROPERTY covers, see the documentation for that routine. See: APP_ITEM_PROPERTY: Individual Property Utilities |
| GET_ITEM_ PROPERTY | Use APP_ITEM_PROPERTY.GET_PROPERTY when getting Oracle E-Business Suite specific properties. Use the Oracle Forms built-in when setting or getting other properties. |
| OPEN_FORM | Use FND_FUNCTION.EXECUTE. This routine is necessary for function security. Both OPEN_FORM and FND_ FUNCTION.EXECUTE cause the POST-RECORD and POST-BLOCK triggers to fire. |
| CLEAR_FORM | Use do_key('clear_form'). This routine raises the exception FORM_TRIGGER_FAILURE if there is an invalid record. You may use this built-in without "do_key" to avoid the additional functionality that comes from going through the trigger. |
| COMMIT | Use do_key('commit_form'). This routine raises the exception FORM_TRIGGER_FAILURE if there is an invalid record. You may use this built-in without "do_key" to avoid the additional functionality that comes from going through the trigger. |
| EDIT_FIELD/ EDIT_ TEXTITEM | Use do_key('edit_field'). This routine raises the calendar when the current item is a date. You may use this built-in without "do_key" to avoid the additional functionality that comes from going through the trigger. |
| VALIDATE | Use APP_STANDARD.APP_VALIDATE instead. This routine navigates to any item that causes navigation failure. You may use this built-in without "do_key" to avoid the additional functionality that comes from going through the trigger. Warning: APP_STANDARD.APP_VALIDATE requires that you follow the button coding standards. See: APP_STANDARD Package and Buttons |
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:
| Variable | Description |
|---|---|
| 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;
Tip: 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).
| Variable | Description |
|---|---|
| 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:
| Variable | Description |
|---|---|
| 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$) 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$) 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;