Creating a Customized Sign-on Form

A customized sign-on form has been a major requirement for Oracle Forms users in most of the client sites where I have worked. A typical requirement of such a customized logon screen is the capability to choose from the various databases available for the database string. The default logon screen for Forms doesn't provide this feature. This section presents an easy solu tion that I have discovered . Figure 2.8 shows an example of how such a screen would look.

Figure 2.8. A customized sign-on form.

In this technique, you nullify the ON-LOGON trigger for the sign-on form conditionally and re-enable logging by using a call to LOGON with proper arguments, after the user completes data entry in the customized logon screen. The sign-on should be the very first form displayed in the application, even before the main menu. Invoke the sign-on form using Forms runtime in the normal manner, with username, password, and database string specified.

This design can be implemented with the following steps:

1. Create a control block named CTRL_BLK with two text items, USERNAME and PASSWORD ; a pop-up list, DATABASE_STRING ; and two push buttons , PB_LOGIN and PB_QUIT.
2. The desired functionality can be achieved by writing an ON-LOGON trigger at the form level and a WHEN-BUTTON-PRESSED trigger for the Login button. The ON-LOGON code is as follows :

   ON-LOGON DEFAULT_VALUE('', 'GLOBAL.tologin'); IF NAME_IN('GLOBAL.tologin') IS NULL THEN NULL; ELSE DECLARE ret_err_code NUMBER; ret_err_text VARCHAR2(1000); BEGIN p_logon(:ctrl_blk.username, :ctrl_blk.password, :ctrl_blk.database_string, ret_err_code, ret_err_text); IF (ret_err_code <> 0) THEN p_show_alert(ret_err_text); RETURN; END IF; END; END IF;

   The WHEN-BUTTON-PRESSED trigger for the Login button would consist of the following code:

   BEGIN ENTER; IF FORM_SUCCESS THEN LOGON(NULL, NULL, FALSE); END IF; END;

   The code for p_logon is as follows:

   PROCEDURE p_logon(un VARCHAR2, pw VARCHAR2, dcs VARCHAR2, o_err_code OUT NUMBER, o_err_text OUT VARCHAR2) IS v_err_code NUMBER; v_err_text VARCHAR2(1000); BEGIN IF dcs IS NOT NULL THEN LOGON(un,pw'@'dcs, FALSE); IF NOT FORM_SUCCESS THEN v_err_code := DBMS_ERROR_CODE; v_err_text := DBMS_ERROR_TEXT; END IF; ELSE LOGON(un,pw, FALSE); IF NOT FORM_SUCCESS THEN v_err_code := DBMS_ERROR_CODE; v_err_text := DBMS_ERROR_TEXT; END IF; END IF; IF v_err_code IS NULL THEN v_err_code := -1; END IF; o_err_code := v_err_code; o_err_text := v_err_text; END;

3. The logic for the Quit button is a simple call to EXIT_FORM.

A few points should be noted here:

• The use of the global variable. This is done to disable the login for the initial run form but enable subsequent logins.
• The NULL arguments to the LOGON built-in. This is done only to make the ON-LOGON trigger fire the subsequent times.
• FORM_TRIGGER_FAILURE is not raised so that repeated clicks of the Login button behave the same as the very first click. Raising FORM_TRIGGER_FAILURE would freeze the login screen until the user corrects the error. This is fine but would defy the functionality of the default Oracle Forms login screen. The customized form would behave exactly as the default one would, but with more features.

On logon failure, the DBMS_ERROR_TEXT captures the error from the database side, and the procedure p_show_alert throws up this message in the form of an alert. For example, in the case of an invalid username or password, you would receive the alert shown in Figure 2.9.

Figure 2.9. This alert message is displayed in the case of an invalid username or password.

The code for the procedure p_show_alert is as follows:

PROCEDURE p_show_alert (ip_errm VARCHAR2) IS alert_id ALERT; alert_button NuMBER; error_msg VARCHAR2(32767); BEGIN alert_id := FIND_ALERT('ALERT_ERROR'); error_msg := ip_errm; SET_ALERT_PROPERTY(alert_id, ALERT_MESSAGE_TEXT, error_msg); alert_button := SHOW_ALERT(alert_id); END p_show_alert;

An alternative way of implementing a customized sign-on form without using an ON-LOGON trigger is to LOGOUT first and then LOGON. Then, the only trigger required is WHEN-BUTTON-PRESSED for the Login button, with code modified as follows:

BEGIN ENTER; IF FORM_SUCCESS THEN LOGOUT; p_logon(:ctrl_blk.username, :ctrl_blk.password, :ctrl_blk.database_string, ret_err_code, ret_err_text); IF (ret_err_code <> 0) THEN p_global_alert(NULL, ret_err_text, 'E', FALSE); Return; END IF; END IF; END;