Skip Headers

Oracle E-Business Suite Developer's Guide
Release 12.1
Part Number E12897-04
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

Using PL/SQL in Oracle E-Business Suite

Overview of Using PL/SQL in Oracle E-Business Suite

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:

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.

See: PL/SQL Stored Procedures

Definitions

Here are definitions of two terms used in this chapter.

Server-side

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

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.

General PL/SQL Coding Standards

Here are general standards you should follow.

Always Use Packages

PL/SQL procedures should always be defined within packages. Create a package for each block of a form, or other logical grouping of code.

Package Sizes

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.

Adding New Procedures to Existing Packages

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.

Using Field Names in Client-Side PL/SQL Packages

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.

Field Names in Procedure Parameters

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.

Using DEFAULT

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.

Use Object IDs

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.

Handling NULL Value Equivalence

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))

Global Variables

Oracle Forms Developer and PL/SQL support different types of global variables:

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).

Database Server Side versus Client Side

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:

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.

Formatting PL/SQL Code

This section contains recommendations for formatting PL/SQL code.

Exception Handling

For exception handling, use the following tips.

Errors in Oracle Forms PL/SQL

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

Errors in Stored 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

Testing FORM_SUCCESS, FORM_FAILURE and FORM_FATAL

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.

Avoid RAISE_APPLICATION_ERROR

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

SQL Coding Guidelines

Follow these guidelines for all SQL that you code:

Triggers in Forms

Follow these general rules for triggers in your forms.

Execution Style

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

KEY- Trigger Properties

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.

WHEN-CREATE-RECORD in Dynamic Query-Only Mode

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;

Resources

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.

Checking Resource Availability

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;

Replacements for Oracle Forms Built-ins

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 CALL_FORM

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

Oracle Forms Built-In With APPCORE Replacements

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

Coding Item, Event and Table Handlers

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.

Coding Item Handlers

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.

Common EVENT Arguments for Item Handlers

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.

The INIT Event

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.

The VALIDATE Event

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.

Item Handler Format

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.

Coding Event Handlers

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).

Common Event Handlers

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

Coding Table Handlers

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.

Acting on a Second Table

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.

Example Client-Side 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 spec you would code for your EMP block

PACKAGE EMP IS
  PROCEDURE Insert_Row;
  PROCEDURE Lock_Row;
  PROCEDURE Update_Row;
  PROCEDURE Delete_Row;
END EMP;

Package body you would code for your EMP block

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;

Example Server-Side Table Handler

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 spec you would code in your form for your EMP block

PACKAGE EMP IS
  PROCEDURE Insert_Row;
  PROCEDURE Update_Row;
  PROCEDURE Lock_Row;
  PROCEDURE Delete_Row;
END EMP;

Package body you would code in your form for your EMP block

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;

Package spec for the server-side table handler (SQL script)

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;

Package body for the server-side table handler (SQL script)

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;