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.
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 StyleIF ... THEN ... ELSE
IF ... THEN ... ELSE
IF ... THEN ... ELSE
END IF
END IF
END IF;Example of Good StyleIF ... 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.
Attention: 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;