Beware the wily SHOW_ALERT

What is the return value of SHOW_ALERT if the user doesn’t click one of the available buttons? According to the documentation (Forms 6i):

SHOW_ALERT
Displays the given alert, and returns a numeric value when the operator selects one of three alert buttons.
Syntax:
SHOW_ALERT (alert_id Alert);
SHOW_ALERT (alert_name VARCHAR2);
Returns a numeric constant corresponding to the button the operator selected from the alert. Button mappings are specified in the alert design.
If the operator selects… Form Builder returns
Button 1 ALERT_BUTTON1
Button 2 ALERT_BUTTON2
Button 3 ALERT_BUTTON3

So for example, we can use this in code like this:

DECLARE
 btn NUMBER(2);
BEGIN
 btn := SHOW_ALERT('MY_ALERT');
 IF btn = ALERT_BUTTON1 THEN
  -- Yes was chosen
 ELSIF btn = ALERT_BUTTON2 THEN
  -- No was chosen
 ELSIF btn = ALERT_BUTTON3 THEN
  -- Cancel was chosen
 END IF;
END;

The alert can have two or three buttons, depending on whether you set the Button 3 Label property. For example, you could have an alert named MY_ALERT with the following settings:

Message: Do you want to save the changes you have made?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: Cancel

If the user clicks Yes, No or Cancel, the function returns one of the three ALERT_BUTTON constants. Simple enough. But what if the user decides to be different and clicks the “X” (close window button)? Alternatively, what if the user presses the “Esc” key on their keyboard?

Testing with Forms 6 (6.0.8.26.0) reveals that the answer depends on whether you have just two buttons defined, or all three. These results are probably the same on other versions, but you should test this on your version to make sure.

If you have three buttons, both the Close Window button and the “Esc” key cause SHOW_ALERT to return ALERT_BUTTON3. If you only have two buttons, the “Esc” key still returns ALERT_BUTTON3, for some reason, whereas the Close Window button returns ALERT_BUTTON2! The moral of the story is, make sure your code handles all three of the ALERT_BUTTON alternatives correctly! Also, make sure that the last button (2nd or 3rd) really does mean “cancel”. A very bad (hypothetical) example would be:

Message: What would you like to do?
Button 1 Label: Create a new record
Button 2 Label: Modify this record
Button 3 Label: Delete all these records

EDIT:
This post seems quite popular, so I thought I’d add what I think are good examples for handling alerts in Forms:

1. Two-button alert

Message: Do you want to XXX?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: [blank]

DECLARE
 btn NUMBER(2);
BEGIN
 btn := SHOW_ALERT('MY_ALERT');
 IF btn = ALERT_BUTTON1 THEN
  -- Yes was chosen
 ELSIF btn = ALERT_BUTTON2 THEN
  -- No was chosen, or Close button clicked
 ELSIF btn = ALERT_BUTTON3 THEN
  -- ESC key pressed
 END IF;
END;

2. Three-button alert

Message: Do you want to XXX?
Button 1 Label: Yes
Button 2 Label: No
Button 3 Label: Cancel

DECLARE
 btn NUMBER(2);
BEGIN
 btn := SHOW_ALERT('MY_ALERT');
 IF btn = ALERT_BUTTON1 THEN
  -- Yes was chosen
 ELSIF btn = ALERT_BUTTON2 THEN
  -- No was chosen
 ELSIF btn = ALERT_BUTTON3 THEN
  -- Cancel was chosen, or Close button clicked, or ESC key pressed
 END IF;
END;

Jeffrey Kemp 20 July 2006

Forms / tips-&-tricks /

About Jeffrey Kemp

Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.