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>