Thursday, July 05, 2012

PL/SQL Exception Handling


This post was tested on Oracle 11.2. It shows that the current PL/SQL block stops executing when an exception occurs. First a table is created to store a list of numbers:

SQL> CREATE TABLE MY_NUMBERS (MY_NUMBER NUMBER)
  2  /

Table created.

SQL>

Then a PL/SQL block is executed. The number 1 is inserted into the table and the transaction is committed. Then the PL/SQL tries to convert the letter A into a number and fails. Control passes to the exception handler, which tests for INVALID_NUMBER, a predefined Oracle exception. An error message is displayed to confirm that the INVALID_NUMBER predefined exception has worked correctly. The block terminates at this point. If this had not been the case, the number 2 would also have been inserted into the table:   

SQL> DECLARE
  2   MY_VARCHAR2 VARCHAR2(1);
  3  BEGIN
  4   MY_VARCHAR2 := '1';
  5   INSERT INTO MY_NUMBERS
  6    SELECT TO_NUMBER(MY_VARCHAR2) FROM DUAL;
  7   COMMIT;
  8   MY_VARCHAR2 := 'A';
  9   INSERT INTO MY_NUMBERS
 10    SELECT TO_NUMBER(MY_VARCHAR2) FROM DUAL;
 11   COMMIT;
 12   MY_VARCHAR2 := '2';
 13   INSERT INTO MY_NUMBERS
 14    SELECT TO_NUMBER(MY_VARCHAR2) FROM DUAL;
 15   COMMIT;
 16  EXCEPTION
 17   WHEN INVALID_NUMBER THEN
 18    DBMS_OUTPUT.PUT_LINE(MY_VARCHAR2||' is invalid');
 19    ROLLBACK;
 20  END;
 21  /
A is invalid

PL/SQL procedure successfully completed.

SQL>

Finally, the contents of the MY_NUMBERS table are displayed to prove that the number 2 was never inserted into the table:

SQL> SELECT MY_NUMBER
  2   FROM MY_NUMBERS
  3   ORDER BY 1
  4  /

MY_NUMBER
----------
         1

SQL>

No comments: