SQL*Forms Technical Tip - Exception Handling

From: Daniel Druker <ddruker_at_us.oracle.com>
Date: Tue, 26 May 1992 22:00:37 GMT
Message-ID: <1992May26.220037.698_at_oracle.us.oracle.com>

Reprinted without permission from Oracle Support Bulletin Boards...

EXCEPTION HANDLERS: TECHNIQUES FOR

VALIDATION AND ERROR HANDLING                   Michael Kim, VMS Sup.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 

PL/SQL provides SQL*Forms designers with powerful exception handlers that are used for validation and error handling. The key to their proper use is the understanding of the predefined exception FORM_TRIGGER_FAILURE. When raised it will cause the currently active trigger to stop and return control (with an error signal) to the entity which called it -- either SQL*Forms itself, or another trigger.  

If the FORM_TRIGGER_FAILURE exception is raised and control is returned to SQL*Forms itself, then post-failure processing will be performed. On the other hand, if one trigger returns control to another trigger (which called it) by raising FORM_TRIGGER_FAILURE, it is the responsibility of the calling trigger to test the success or failure of the called trigger and proceed accordingly.  

The following examples will show the use of the ON-VALIDATE-FIELD trigger, packaged functions such as FORM_SUCCESS and FORM_FAILURE, PL/SQL functions like SQLCODE and SQLERMM, and exceptions such as FORM_TRIGGER_FAILURE (using the Dept table).  

Case A: Force the user to enter a valid code. These triggers will ~~~~~~ validate DEPTNO against the existing numbers in the table. If

        it succeeds, then it will continue with more steps.  If
        unsuccessful, then it will highlight the field and warn the
        user.
 
Step 1: Write the ON-VALIDATE-FIELD Trigger.
        
        select deptno into :deptno from dept where :deptno = deptno;
        exception
                when NO_DATA_FOUND then
                        message('You have entered an wrong value');
                raise FORM_TRIGGER_FAILURE;
 

Step 2: Write the KEY-NXTFLD Trigger.  

        NEXT_FIELD; /* Will cause field-validation */  

        if FORM_SUCCESS then     /* Continue once field is      */
                                 /* Successfully validated      */ 
                do processing ...
 
        end if;
                
 
Case B: Branch depending on queries.  This example shows how you can 
        trap multiple select statements in a single trigger with
        exception  handlers when they satisfy preselected conditions.
        
        BEGIN
          SELECT deptno 
            INTO :deptno 
            FROM dept 
           WHERE deptno = :deptno;
           IF :deptno = 10 THEN
             RAISE FORM_TRIGGER_FAILURE;
           END IF;
        EXCEPTION
          when NO_DATA_FOUND then
             message('No data found.');
          when TOO_MANY_ROWS then
             message('Too many rows.');
          when FORM_TRIGGER_FAILURE then
             message('deptno can not be 10.);
          when OTHERS then
             raise FORM_TRIGGER_FAILURE;
        end;
        begin
        select deptno into :deptno from dept where deptno = 20;
        exception
        when NO_DATA_FOUN then
                message('No data found.');
        when OTHERS then
                raise FORM_TRIGGER_FAILURE;
        end;
        
Case C: To trap Oracle (ORA) errors, there are three different
        options:  
 


           (1) Predefined PL/SQL Exceptions,

           (2) User-Defined Exceptions,


           (3) SQLCODE/SQLERRM Functions.


 
        The examples below shows how one can trap ORA-0001, ORA-1013,
        and ORA-1438 errors.
 
        ON-INSERT trigger:
        ~~~~~~~~~~~~~~~~~~
        DECLARE
           /*
           || Declare a user-defined exception and associate its
           || raising to the occurrence of an ORA-1013 error.
           */
           user_requested_abort   EXCEPTION;
           PRAGMA EXCEPTION_INIT(user_requested_abort, -1013);
        BEGIN
 
          /* 
          || Perform the INSERT
          */
          INSERT INTO dept 
          VALUES (:dept.deptno,:dept.dname,:dept.loc);
 
        EXCEPTION
          
          /*
          || Trap ORA-0001 using a predefined PL/SQL exception
          */
          when DUP_VAL_ON_INDEX then
             message (SQLERRM);
             raise FORM_TRIGGER_FAILURE;
         
          /*
          || Trap ORA-1013 using a user-defined exception associated
          || to the SQLCODE -1013.
          */
          when USER_REQUESTED_ABORT then
             message('You pressed Control-C');
             raise FORM_TRIGGER_FAILURE;
 
          /*
          || Use SQLCODE and SQLERRM to determine what error occurred
          */ 
          when OTHERS then
             if SQLCODE = -1438 then    /* ORA-01438: value larger  */
                                        /* than specified precision */
                                        /* allows.                  */ 
                message (SQLERRM);
                raise FORM_TRIGGER_FAILURE;
             /*
             || Unexpected Error
             */
             else
                message ('Unknown Error');
                raise FORM_TRIGGER_FAILURE;
             end if;
        end;    
 
Case D: Turn off insert record processing for a block.  Create a
        PRE-INSERT trigger at the block level with the following text:
 
        message ('Function not allowed.');
        raise FORM_TRIGGER_FAILURE;
 

---

Consult the Sql*Forms Designer's Manual and PL/SQL Manual for more details on the use of exception handlers.

• Dan

Daniel Druker
Senior Consultant
Oracle Corporation                    

---

| Dan Druker                    |  work 415.506.4803                          |
| oracle*mail ddruker.us1       |  internet: ddruker_at_us.oracle.com            |
-------------------------------------------------------------------------------

Disclaimer: These are my opinions and mine alone, and don't reflect the views or position of my employer. Received on Wed May 27 1992 - 00:00:37 CEST