Unit IV

Preparing for OCP Exam 4: Build Forms II

Chapter 16

Forms Processing

In this chapter, you will understand and demonstrate knowledge in the following areas:

• Forms Runtime messages and alerts
• Data validation
• Navigation
• Transaction processing

This chapter builds on the knowledge you gained in the preceding chapter about triggers and shows you how to apply it in several new areas. The chapter starts with an introduction to the messages produced by the Forms Runtime program. With that basis established, it goes on to show how you can create and present your own messages to the user. You will also learn about numerous triggers and techniques that can ensure the integrity of the data entered through your applications. Next, you will see how to move the focus from object to object in an application’s GUI programmatically. The chapter wraps up by thoroughly covering the processes and triggers used in transaction processing, including information on how to use Oracle sequences and array DML in your applications. The OCP Exam 3 will consist of test questions in this subject area worth 23 percent of the final score.

In this section, you will cover the following points related to Forms Runtime messages and alerts:

• Introduction to the default messaging
• Handling errors using built-in subprograms
• Controlling system messages
• Creating and controlling alerts

At some point in your application’s operation you will need to communicate with the user in a way they cannot ignore. The message may simply be informative; it may require them to make a decision; or it could be warning them of a problem. In this section, you will learn how to create these messages and redirect your code’s execution path based on the user’s response. You will also learn the basics of trapping error conditions. This is an essential part of creating robust, reliable, bulletproof applications.

The Forms Runtime program has the ability to generate a wide variety of error messages in response to anomalous conditions. It displays these messages in either of two places: the message line within the console at the bottom of the Forms Runtime display, or in pop-up message windows called alerts. Each message has an internally assigned severity level that defines how strongly it tries to be seen. The severity levels can be grouped into the following categories:

As the last row in this table indicates, by setting a system variable named SYSTEM.MESSAGE_LEVEL in your code you can instruct the Forms Runtime program to suppress messages whose severity level is below a certain value. The technique to do this will be covered in detail later in this chapter.

Forms Runtime messages are divided into four types. The first type is the Working message, also known as a Hint. These Hints tell the user that the Forms Runtime program is processing data or instructions. They appear in the console message line. They are useful for messages that the user can ignore without interfering with processing. The second type of Forms Runtime message is the Informative message, which appears when the user needs to acknowledge the message but will not be required to make a choice in order to proceed. Informative messages consist of the message text, an OK button, and the Information icon. They are useful for messages such as "No records matching your criteria were found." The third type of Forms Runtime message is the Warning message, which appears when the user needs to make a choice before processing can continue. Warning messages consist of the message text, up to three buttons, and the Warning icon. They are useful when you want the user to answer a question or verify an action before processing proceeds. The last type of Forms Runtime message is the Error message, which appears when a condition has occurred that prevents the user from continuing whatever action they were taking. Error messages consist of the message text, the OK and Help buttons, and the Error icon.

Errors are bound to happen, and it is important that your application be ready to handle them gracefully. When a Forms application encounters an error, it raises an exception, halts execution of the PL/SQL code that was active when the error occurred, and looks in the current PL/SQL block for an EXCEPTION section to tell it what to do about the error. Developer/2000 divides errors into two types: internal exceptions, which include all the situations for which Oracle Server or Oracle Forms already has preprogrammed error messages; and user-specified exceptions, which are defined by the developer. Form Builder provides a variety of built-ins for error handling. Table 16-1 shows the relevant built-ins along with explanations of each one.

All of the built-ins shown in Table 16-1 can be used in any PL/SQL code in your application. The ON-MESSAGE and ON-ERROR triggers can be defined at the form, block, or item level; form level is recommended, because it can be difficult for the Forms Runtime program to trap block- and item-level errors while it is performing internal navigation functions like committing records. The following code demonstrates a simple error-trapping routine that works when installed as an ON-ERROR trigger at the form level. This routine traps record-insertion problems and displays a message in the console’s message line.

DECLARE
DBERR_NUM number := dbms_error_code;
ERR_NUM number := error_code;
ERR_TXT varchar2(80) := error_text;
ERR_TYP varchar2(3) := error_type;
BEGIN
if ERR_NUM = 40508 then
if DBERR_NUM = -1 then
message('Primary key for new record duplicates key in existing record.');
else
message('Cannot insert this record...reason undetermined.');
end if;
else
message(ERR_TYP||'-'||to_char(ERR_NUM)||': '||ERR_TXT);
end if;
end;

There are two ways you can control system messages in Form Builder. The first approach is very similar to the technique you just learned for handling errors: you simply place the trapping code in an ON-MESSAGE trigger instead of an ON-ERROR trigger, and the trapping code contains MESSAGE_CODE, MESSAGE_TEXT, and MESSAGE_TYPE built-ins instead of ERROR_CODE, ERROR_TEXT, and ERROR_TYPE built-ins. An example of this type of code follows, utilizing the DISPLAY_ERROR built-in to show the error screen:

DECLARE
MSG_NUM number := message_code;
MSG_TXT varchar2(80) := message_text;
MSG_TYP varchar2(3) := message_type;
BEGIN
if MSG_NUM = 40301 then
message('No records matching your criteria were found.');
DISPLAY_ERROR;
else
message(MSG_TYP||'-'||to_char(MSG_NUM)||': '||MSG_TXT);
end if;
end;

If your goal is to suppress low-priority messages entirely, you can do so using the SYSTEM.MESSAGE_LEVEL system variable. For instance, when the Forms Runtime program is at the first record of a table and the user clicks on the Previous Record button, the console’s message line displays a rather terse "FRM-40100: At first record" in the console’s message line. You could use the ON-MESSAGE trigger to change the message, using code similar to that shown in the previous listing. Or, you can set the SYSTEM.MESSAGE_LEVEL system variable so that the Forms Runtime program doesn’t display the message at all. The severity level of the FRM-40100 message is 5, so the following instruction in a Form module’s WHEN-NEW-FORM-INSTANCE trigger will keep the message from displaying:

:system.message_level := 5;

One other type of system message that is sometimes useful to suppress is the "Working…" message that appears in the console’s message line while records are being retrieved. The message itself isn’t the problem, but rather the fact that it usually causes the screen to update, which can generate unwanted delays. Because the message is generally useful, you may find it makes sense to suppress it only when you need to, rather than globally. Code like the following will do it:

:system.suppress_working := 'TRUE';
go_block('EMPLOYEE');
execute_query;
go_block('EMPLOYEE_SKILL');
execute_query;
go_block('EMPLOYEE');
:system.suppress_working := 'FALSE';

Form Builder offers a premade dialog box called an alert that shows the user a message of your choosing and provides one, two, or three buttons for their response. Your PL/SQL code then reads the user’s response and selects subsequent actions based on it. There are three types of alerts: Note, Caution, and Stop. To experiment with alerts in your own application, take the following steps.

Open your AV_DATA_SOUND application. In the Object Navigator, click on the Alerts node, followed by the Create button. An alert object will appear in the Object Navigator (as opposed to all the other objects, which are probably snoozing). Open the alert’s Property Palette, and change its Name property to TEST_ALERT_NOTE. Change its Title property to Test Alert Note, its Message property to This is your sample message for the Test Alert Note, its Alert Style property to Note, and its Button 1 Label property to OK. If the Button 2 Label or Button 3 Label properties have contents, delete those contents; this will cause your alert to only display one button. Your alert is now complete. To make it display, you must create a trigger or subprogram that calls for it. To do that, open the AV_DATA_SOUND canvas and create a new push button on it named TEST_ALERT_NOTE. Set the button’s Label property to Test Alert Note. Right-click on the button and select SmartTriggers from the context menu that appears. Select the WHEN-BUTTON-PRESSED trigger, and enter the following code in the PL/SQL Editor.

DECLARE
V_ALERT_USER_RESPONSE number;
BEGIN
V_ALERT_USER_RESPONSE := show_alert('TEST_ALERT_NOTE');
END;

Compile your code and then close the PL/SQL Editor. Run your application, click on the Execute Query button to populate the form with records, and click on your Test Alert Note button. You should see a dialog box appear that looks like Figure 16-1. Click on the OK button to dismiss the alert and then exit the Forms Runtime program to return to Form Builder.

Informative alerts like this are useful, but there will be times when you want the alert to ask the user to make a choice. The SHOW_ALERT built-in returns specific numeric constant values depending on which of its three buttons the user selects. If the user clicks on the first button, the SHOW_ALERT built-in returns the value alert_button1, regardless of the label the button displays. If the user clicks on the second button, the SHOW_ALERT built-in returns the value alert_button2. Clicking on the third button, or exiting the alert without choosing a button, generates a return value of alert_button3. To see this in action, create another alert in the Object Navigator and name it TEST_ALERT_STOP. Change the alert’s Title property to Test Alert Stop, and its Message property to You are about to do something destructive. Continue? Change its Alert Style property to Stop, and its three button labels to Yes, Help, and No. Then open your AV_DATA_SOUND canvas once again and add another push button. Set the button’s Name property to TEST_ALERT_STOP and its Label property to Test Alert Stop. Right-click on the button and select SmartTriggers from the context menu that appears. Select the WHEN-BUTTON-PRESSED trigger, and enter the following code in the PL/SQL Editor:

DECLARE
V_ALERT_USER_RESPONSE number;
BEGIN
V_ALERT_USER_RESPONSE := show_alert('TEST_ALERT_STOP');
if V_ALERT_USER_RESPONSE = alert_button1 then
message('User selected button 1.');
elsif V_ALERT_USER_RESPONSE = alert_button2 then
message('User selected button 2.');
else message('User selected button 3 or cancelled alert without making a selection.');
end if;
END;

Notice how this code compares the variable V_ALERT_USER_RESPONSE, which stores the user’s response, with numeric constants named alert_button1, alert_button2, and alert_button3. This is how it determines which button the user clicked.

Compile your code and then close the PL/SQL Editor. Run your application, click on the Execute Query button to populate the form with records, and click on your Test Alert Stop button. You should see a dialog box appear that looks like that in Figure 16-2. Each time you select a button, your message will appear in the console’s message line. Experiment now by clicking on each of the three available buttons. Find out what happens when you avoid the buttons by closing the alert with the esc key. When you are done, exit the Forms Runtime program and return to Form Builder.

In this section, you will cover the following points about data validation:

• Effects of the validation unit on a form
• Introduction to Form Builder validation properties
• Controlling validation using triggers

As anyone who has studied popular psychology can tell you, validation is an important part of any interaction. In this section, you will learn everything you need to know to ensure that your data is properly validated. You will see how a validation unit effects a form, learn about the validation properties available in Form Builder, and practice controlling data validation by employing triggers.

The term validation refers to the process of ensuring that data satisfies whatever requirements you have specified. For instance, it makes sense to use validation to guarantee that a State field contains one of the 50 recognized two-character abbreviations; that a gender field contains only M or F; and that a salary figure is greater than zero. Validation can occur in the Oracle database, as well as in the client application. On the client side, validation can occur each time the user enters an item, completes a record, leaves a data block, or leaves a form. This is known as the validation unit: the largest chunk of information a user can enter before the form starts the validation process. You specify this at the form module level, using a property named Validation Unit. By default, the validation unit is at the Item level, which causes the Forms Runtime program to perform validation when the user navigates out of an item, presses the enter key, or attempts to commit their changes to the database. If you change the validation unit to Record, data will not be evaluated until the user navigates out of the current record, or attempts to commit changes; when this occurs, all items within the current record will be validated. If the validation unit is set to Data block or Form, the Forms Runtime program waits until the user tries to navigate out of the block or form, respectively, or until they attempt to commit changes to the database. At that time, all records in the block are validated.

Client-side validation properties are usually defined at the item level. Text items have a number of properties that affect how they are validated. These properties are listed in Table 16-2. If the text item has an attached List of Values, setting the item’s Validate From List property to Yes will cause the Forms Runtime program to check what the user has typed against the values in the LOV’s first column. If no LOV entry exactly matches what the user entered, the Forms Runtime program opens the LOV and autoreduces its contents so that only those rows that match what the user typed will be displayed. For instance, if the user enters just A in a State field with an attached LOV, when the field is validated the LOV will open and only those states that start with "A" will be shown.

You can augment the default validation of items or records by employing triggers. All you have to do is put PL/SQL code within the appropriate trigger to check the value you are concerned about. You can use the WHEN-VALIDATE-ITEM trigger to perform item-level validity checking. It is the last trigger to fire during item validation for new or changed items, and it can be assigned to the item, block, or form level. For record-level validity checking, the WHEN-VALIDATE-RECORD trigger comes into play. It is the last trigger to fire during record validation for new or changed records, and it can be assigned to the block or form level.

As an example, you could place the following PL/SQL code in a WHEN-VALIDATE-ITEM trigger connected to the EMPLOYEE_SKILL.SKILL_LEVEL item. Doing so would ensure that the skill values stay between 1 and 5. Combined with the fact that the SKILL_LEVEL item has a NUMBER(1,0) datatype that excludes decimal places, this trigger ensures that the user can only the values 1, 2, 3, 4, or 5.

if not(:EMPLOYEE_SKILL.SKILL_LEVEL BETWEEN 1 AND 5) then
message('WHEN-VALIDATE-ITEM trigger failed on field '||:system.trigger_field);
raise form_trigger_failure;
end if;

In this section, you will cover the following points about navigation:

• Internal versus external navigation
• Using navigation triggers
• Introduction to built-ins that cause navigation

Controlling where the focus is in your application, and what happens at each point when movement occurs, is essential to creating a smooth, stable experience for the user. This section provides a thorough introduction to navigation in Form Builder applications. It starts by explaining the difference between internal and external navigation. You will learn how to employ navigation triggers to achieve an extremely high degree of control over movement-related actions, and you will be presented with step-by-step tables showing the order in which these triggers fire. After that, you will discover all the built-ins Form Builder provides for controlling movement and focus from within your PL/SQL code.

As a programmer, you undoubtedly already understand the difference between a movement the user makes from one object to another, and goto-like functions in code that occur behind the scenes. A similar dynamic exists in the realm of form navigation in Form Builder, and understanding it will help you determine where to place triggers to achieve the desired scope. From the perspective of the Forms Runtime program, clicking on any input or noninput item that fires a trigger, or pressing a keyboard key to move from one object to another, creates an external navigation event. For instance, consider a user who is working with a form displaying two blocks. The user sees an object they wish to change, clicks on it, and the focus naturally moves to that object. That is external navigation. However, what the Forms Runtime program actually does to move from one object to another is substantially more involved. In the example just offered, the Forms Runtime program would respond to the user’s click by going "up the tree" of object hierarchy: validating the item the user was on at the time, leaving the item, moving up to the next-larger object in the item’s hierarchy (a record in this case) and validating it, leaving the record, and leaving the block that contained the record. It would then go down the desired branch by entering the destination block, entering the destination record, entering the destination item, preparing the block for input, preparing the record for input, and preparing the item for input. This is internal navigation, and in this case it consisted of 11 discrete events, each of which could fire its own trigger.

Navigational triggers fire when internal navigation is occurring. There are triggers for each level in the object hierarchy: form, block, record, and item. Table 16-3 lists the navigation triggers, describes each one, and identifies the level at which each can be used. To help you visualize the order of trigger firing and how it relates to actions perceivable by the user, Tables 16-4, 16-5, and 16-6 provide a chronology of user actions, triggers fired in response, and perceivable results when the user starts a Forms Runtime application, moves among records, and exits the Forms Runtime program.

A key requirement of coding routines in a GUI environment is controlling the focus within the GUI. Form Builder provides several built-in subprograms you can use in your PL/SQL code to move the focus to different objects. These built-ins are shown in Table 16-7. These navigation built-ins are restricted, so they cannot be used in the PRE- or POST- navigational triggers. Instead, place them inside WHEN- triggers such as WHEN-BUTTON-PRESSED, WHEN-CHECKBOX-CHECKED, WHEN-NEW-FORM-INSTANCE, WHEN-NEW-BLOCK-INSTANCE, WHEN-NEW-RECORD-INSTANCE, and WHEN-NEW-ITEM-INSTANCE.

In this section, you will cover the following points about transaction processing:

• Commit processing, transaction processing, and triggers
• Allocating automatic sequence numbers to records
• Implementing array DML

The information in this section will enable you to exert a high degree of control over the behind-the-scenes processing of your application’s records. You will begin with a detailed account of the triggers available for your use during commit and transaction processing. Next, you will create an autonumbering sequence for one of your application’s tables, and instruct the relevant data block in your application to use that sequence as its source for ID numbers. Finally, you will see how to cut transaction-processing time to a minimum using array DML.

The phrase "commit processing" refers to posting data from a client application to the database, and then committing that data. Posting the data involves sending all of the client application’s inserts, updates, and deletes to the database. Committing the data involves making the posted data permanent in the database, and therefore available to other users. A commit command causes both post and commit operations to take place, although you can use the post command to cause only the posting stage to occur; this is especially useful when you have entered new data in a master form and are going to open a detail form to augment it. Table 16-8 shows the most important commit and transaction triggers available in Form Builder applications.

The actual process of committing records and performing other transactions varies depending on the nature of the transaction. Table 16-9 shows the steps that occur when records are retrieved from a database into the client application. Tables 16-10 through 16-12 show the steps when records are added, changed, and deleted.

When Oracle performs the commit, it processes record deletions before inserts or updates, because getting the deleted records out of the way could speed up subsequent update processes. Next it processes updates, again with the idea that waiting until after new records are inserted would only cause the update process to churn through additional records that presumably do not need updating. Finally, the new records are inserted. The whole process is depicted in Table 16-13, which shows the triggers that fire if you commit after adding one record, changing one record, and deleting one record. Looking at the chronology of events points out just how many triggers are available to you for controlling transactions.

A sequence is an Oracle construct that produces unique numbers, usually in sequential order. You can easily instruct your Form Builder applications to use sequences to get unique ID values for new records. To experiment with this, create a sequence using the following code.

create sequence SEQ_EMPLOYEE
start with 1050
nomaxvalue
nocycle;

commit;

Now, open your sample application in Form Builder, open the EMPLOYEE_2 data block node, and then the Items node beneath it. Select the EMPLOYEE_ID item and set its Initial Value property to :sequence.SEQ_EMPLOYEE.nextval to tell the item to retrieve ID values from the sequence you just created. Save the form and run it. When it opens in the Forms Runtime program, you will immediately see that the Employee ID field is already populated with a new number. Go ahead and fill out the new record with last name Faltiss, first name Jeremy, hire date 31-DEC-1998, salary 102500, department Information Technology, and profit sharing True. Save your record and exit the Forms Runtime program.

You can control how many record inserts, updates, and deletes the Forms Runtime program can send in a single commit transaction. Increasing this number can improve overall application performance because each commit transaction has a certain amount of inherent overhead; combining multiple records into a single commit reduces that overhead in comparison with issuing a commit for every individual record. The trade-off is that those uncommitted records have to be stored somewhere, and of course that somewhere is the client computer’s memory. The larger the batch of records the client computer stores between each commit, the more memory it requires.

The need for this ability, known as array data manipulation language (DML), varies depending on the content of each data block. Therefore you can set the array DML quantity for each data block individually. The name of the property is DML Array Size. The Developer/2000 demo file <oracle_home>\tools\devdem20\demo\forms\arraydml.fmx contains a powerful example of the speed increases you can achieve by implementing array DML.

In this chapter, you have covered quite a bit of information on forms processing. The topics covered include Runtime messages and alerts, data validation, external and internal navigation, and transaction processing. All in all, this chapter comprises about 23 percent of material tested on OCP Exam 3.

The first area you covered was Runtime messages and alerts. Topics included an introduction to Form Builder’s default messaging, how to handle errors with built-in subprograms, controlling system messages, and creating and controlling alerts. Messages can be presented in the console’s message line or in pop-up alert windows. Every message has an internally assigned severity level that defines how strongly it tries to be seen. Forms Runtime messages are divided into four types. The first Forms Runtime message type is the Working message, also known as a Hint. These hints tell the user that the Forms Runtime program is processing data or instructions. They appear in the console message line. The second type of Forms Runtime message is the Informative message, which appears when the user needs to acknowledge the message but will not be required to make a choice before the message will go away. Informative messages consist of the message text, an OK button, and the Information icon. The third Forms Runtime message type is the Warning message, which appears when the user needs to make a choice before processing can continue. Warning messages consist of the message text, up to three buttons, and the Warning icon. The last type of Forms Runtime message is the Error message, which appears when a condition has occurred that prevents them from continuing whatever action they were taking. Error messages consist of the message text, the OK and Help buttons, and the Error icon.

When a Forms application encounters an error, it raises an exception, halts PL/SQL code execution, and looks in the current PL/SQL block for an EXCEPTION section to tell it what to do about the error. Developer/2000 divides errors into two types: internal exceptions, which include all the situations for which Oracle Server or Oracle Forms already has preprogrammed error messages; and user-specified exceptions, which are defined by the developer. Form Builder provides a variety of built-ins for error handling, including DBMS_ERROR_CODE, DBMS_ERROR_TEXT, ERROR_CODE, ERROR_TEXT, ERROR_TYPE, MESSAGE_CODE, MESSAGE_TEXT, and MESSAGE_TYPE. All of these can be used in any PL/SQL code in your application. It is best to define the ON-MESSAGE and ON-ERROR triggers at the form level, because it can be difficult for the Forms Runtime program to trap block- and item-level errors while performing internal navigation functions like committing records. When you are trapping system messages with the ON-MESSAGE trigger, the useful built-ins are MESSAGE_CODE, MESSAGE_TEXT, and MESSAGE_TYPE, instead of ERROR_CODE, ERROR_TEXT, and ERROR_TYPE. You can use the SYSTEM.MESSAGE_LEVEL system variable to suppress low-level system messages entirely by placing a command like system.message_level := 5; in your form’s WHEN-NEW-FORM-INSTANCE trigger. You can also control the display of the "Working…" message in the console’s message line by including system.suppress_working := 'TRUE'; and system.suppress_working := 'FALSE'; commands at appropriate points in your code.

Next you learned about alerts. An alert is a modal dialog box containing message text, an icon indicating the severity of the alert, and anywhere from one to three buttons. Alerts are displayed via the PL/SQL command show_alert, which is usually placed in a trigger. The PL/SQL code can respond to the user’s button selection by executing different code segments for each button. There are three types of alerts: Note, Caution, and Stop. The only inherent difference between them is the icon they display with the text, although you will generally use them to convey the alert’s importance to the user. The button the user selects is returned to the PL/SQL code in the form of numeric constants named alert_button1, alert_button2, and alert_button3.

The next subject you covered was data validation. Topics here included the effects of the validation unit on a form, an introduction to Form Builder’s validation properties, and how to control validation using triggers. The term "validation" refers to the process of ensuring that data satisfies whatever requirements you have specified. Validation can occur in the Oracle database, as well as in the client application. On the client side, text items have several properties that assist with data validation, including Data Type, Maximum Length, Fixed Length, Required, Lowest Allowed Value, Highest Allowed Value, Format Mask, Insert Allowed, Update Allowed, Update Only If NULL, and Validate From List. If the text item has an attached List of Values, setting the item’s Validate From List property to Yes causes the Forms Runtime program to check the user’s entry against the LOV, and if no matching LOV value exists, the LOV is opened and autoreduced based on the user’s entry. You can also utilize triggers to augment the default validation of items or records. The WHEN-VALIDATE-ITEM trigger performs item-level validity checking, while the WHEN-VALIDATE-RECORD trigger performs record-level validity checking. The form-level property Validation Unit determines the largest chunk of information a user can enter before the form fires validation triggers. The validation unit can be item, record, block, or form, with the default validation unit being the smallest one, item.

Next, your attention turned to navigation. There are two types of navigation: external, which encompasses any change in focus on the GUI, and internal, which consists of the steps taken by the Forms Runtime program as it moves up and down object hierarchies to execute external navigation initiated by the user. Form Builder provides navigational triggers that fire at different stages of the navigation process. There are triggers for each level in the object hierarchy: form, block, record, and item. These triggers include PRE-FORM, PRE-BLOCK, PRE-TEXT-ITEM, POST-TEXT-ITEM, WHEN-NEW-FORM-INSTANCE, WHEN-NEW-BLOCK-INSTANCE, WHEN-NEW-RECORD-INSTANCE, WHEN-NEW-ITEM-INSTANCE, POST-BLOCK, and POST-FORM. Form Builder also provides a variety of built-ins to cause navigation, including GO_ITEM, GO_RECORD, GO_BLOCK, GO_FORM, FIRST_RECORD, LAST_RECORD, NEXT_RECORD, PREVIOUS_RECORD, UP, and DOWN. Because these navigation built-ins create movement, they are restricted and cannot be used in PRE- or POST- navigational triggers, so as to avoid endless navigation loops. They can, however, be used in WHEN- triggers.

The final area you covered was transaction processing. Committing, or saving, data to the database actually includes two steps: posting the data to the database (available by itself via the post command) and then committing the data to make it permanent. Many triggers can fire during a commit transaction, including PRE-BLOCK, PRE-COMMIT, PRE-DELETE, PRE-FORM, PRE-INSERT, PRE-QUERY, PRE-RECORD, PRE-SELECT, PRE-TEXT-ITEM, and PRE-UPDATE; POST-BLOCK, POST-CHANGE, POST-DATABASE-COMMIT, POST-FORM, POST-FORMS-COMMIT, POST-INSERT, POST-QUERY, POST-RECORD, POST-SELECT, POST-TEXT-ITEM, and POST-UPDATE; and WHEN-NEW-BLOCK-INSTANCE, WHEN-NEW-FORM-INSTANCE, WHEN-NEW-RECORD-INSTANCE, and WHEN-NEW-ITEM-INSTANCE. When Oracle performs the commit, it processes record deletions first, then updates, and then inserts.

Implementing a sequence to provide unique numbers for a data block item is quite simple. After you create the database sequence with the create sequence command, you set the Initial Value property for the appropriate data block item to :sequence.sequence_name.nextval. Implementing array DML to gain the speed benefits of batch transactions is equally easy: you just set the relevant data block’s DML Array Size property to the number of records that should be in each batch.