MESSAGE-HANDLING TRIGGERS

Form Builder automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.

1. On-Error

Replace a default error message with a custom error message, or to trap and recover from an error. Usage Notes

Use an On–Error trigger for the following purposes:

• To trap and recover from an error

• To replace a standard error message with a custom message Use the ERROR_CODE,

ERROR_TEXT, ERROR_TYPE, DBMS_ERROR_TEXT, or DBMS_ERROR_CODE built–in function in an On–Error trigger to identify a specific error condition.

• In most cases, On–Error triggers should be attached to the form, rather than to a block or item. Trapping certain errors at the block or item level can be difficult if these errors occur while Oracle Forms is performing internal navigation, such as during a Commit process.

Example:

DECLARE

lv_errcod NUMBER := ERROR_CODE; lv_errtyp VARCHAR2(3) := ERROR_TYPE; lv_errtxt VARCHAR2(80) := ERROR_TEXT; BEGIN

IF (lv_errcod = 40nnn) THEN /*

** Perform some tasks here */

ELSIF (lv_errcod = 40mmm) THEN /* ** More tasks here */ ...

ELSIF (lv_errcod = 40zzz) THEN ** More tasks here */ ELSE Message(lv_errtyp||’–’||to_char(lv_errcod)||’: ’||lv_errtxt); RAISE Form_Trigger_Failure; END IF; END; 2. On-Message

To trap and respond to a message; for example, to replace a default message issued by Form Builder with a custom message.

Usage Notes:

Use an On–Message trigger for the following purposes: • To trap and respond to an informative message

• To replace a standard informative message with a custom message • To exclude an inappropriate message

Example:

The following example responds to an error message by displaying an alert that gives the user a message and gives the user the choice to continue or to stop:

DECLARE

alert_button NUMBER;

lv_errtype VARCHAR2(3) := MESSAGE_TYPE; lv_errcod NUMBER := MESSAGE_CODE; lv_errtxt VARCHAR2(80) := MESSAGE_TEXT; BEGIN

IF lv_errcod = 40350 THEN

alert_button := Show_Alert(’continue_alert’); IF alert_button = ALERT_BUTTON1 THEN

... ELSE ... END IF; ELSE Message(lv_errtyp||’–’||to_char(lv_errcod)||’: ’||lv_errtxt); RAISE Form_Trigger_Failure; END IF; END; F.QUERY-TIME TRIGGERS

Query-time triggers fire just before and just after the operator or the application executes a query in a block. 1. Pre-Query

Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.

This Pre-Query trigger on the S_ORD block only permits queries if there is a restriction on either the Order ID, Date Ordered, or Date Shipped. This prevents attempts at very large queries.

• To test the operator’s query conditions, and to fail the query process if the conditions are not satisfactory for the application

• To add criteria for the query by assigning values to base table items

• A Pre-Query trigger fires before a query executes. Use it to check or modify query conditions.

Make sure the user has given one of the two Columns which we have indexed in their search criteria, otherwise fail the query with a helpful message

A] IF :Employee.Ename IS NULL AND :Employee.Mgr IS NULL THEN

Message(’Supply Employee Name and/or Manager Id ’||’for Query.’); RAISE Form_Trigger_Failure;

END IF;

B] [ exact_match – Check Box

User can specify if or not a query condition for a customer name should exactly match the table value. [ Set the initial value property to “Y”. ]

IF nvl(:control.exact_match,’Y’)=’N’ then

:S_Customer.name:=’%’||:S_customer.name || ‘%’; END IF;

2. Post-Query

Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.

This trigger is defined at block level or above. Post-Query fires for each record that is fetched into the block as a result of a query. Note that the trigger only fires on the initial fetch of a record not when a record is subsequently scrolled back into view a second or third time.

Use Post-Query as follows:

• To populate non database items as records are returned from a query

• To calculate statistics

• A Post-Query trigger fires as each record is fetched (except array processing). Use it to perform calculations and populate additional items.

This Post-Query trigger on the S_ORD block selects the total count of line items for the current Order, and displays this number as a summary value in the non base table item :Lineitem_count.

Begin

SELECT COUNT(ord_id) INTO :S_ORD.lineitem_count FROM S_ITEM WHERE ord_id = :S_ORD.id; End;

Example:

DECLARE

CURSOR lookup_payplan IS SELECT Payplan_Desc FROM Payplan WHERE Payplan_Id = :Employee.Payplan_Id;

CURSOR lookup_area IS SELECT Area_Name FROM Zip_Code WHERE Zip = :Employee.Zip; BEGIN

/* Lookup the Payment Plan Description given the Payplan_Id in the Employee Record just fetched. ** Use Explicit Cursor for highest efficiency.*/

OPEN lookup_payplan;

FETCH lookup_payplan INTO :Employee.Payplan_Desc_Nondb; CLOSE lookup_payplan;

/* ** Lookup Area Descript given the Zipcode in ** the Employee Record just fetched. Use Explicit ** Cursor for highest efficiency. */

OPEN lookup_area;

FETCH lookup_area INTO :Employee.Area_Desc_Nondb; CLOSE lookup_area;

END;

A query fetched 10 records How many times does a PRE-QUERY Trigger and POST-QUERY Trigger will get executed? PRE-QUERY fires once.

POST-QUERY fires 10 times.

• A Pre-Query trigger fires before a query executes. Use it to check or modify query conditions. • A Post-Query trigger fires as each record is fetched (except array processing). Use it to perform

calculations and populate additional items.

What is a difference between pre-select and pre-query?

Fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the statement is actually issued.

The pre-query trigger fires just before oracle forms issues the select statement to the database after the operator as define the example records by entering the query criteria in enter query mode.

Pre-query trigger fires before pre-select trigger.