Systems Engineering and RDBMS

Back to the Basics: Exception Handling in PL/SQL

Posted by decipherinfosys on November 29, 2007

In our previous blog posts, we had covered the scenarios of using multiple exceptions and using NO_DATA_FOUND exception when used with bulk collection. In this blog post, we will cover the basic exception handling scenarios.

Exceptions are constructs used in PL/SQL programming to handle error conditions. Oracle provides some pre-defined exceptions. These exceptions are very specific (to prevent duplicate record in the table) to more general (to handle any errors). Users can also define his or her own exceptions. When dealing with exceptions, we have to handle the specific exceptions first before handling the general exceptions. Let us start with example. We will first create the table.

CREATE TABLE TEST
(
TEST_ID NUMBER NOT NULL,
TEST_NAME VARCHAR(15) NOT NULL,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
/

Now we will try to insert two records with the same TEST_ID. Oracle provides pre-defined exception WHEN DUP_VAL_ON_INDEX to handle the error due to inserting duplicate record. Oracle also provides WHEN OTHERS exception to handle any error. Let us execute following PL/SQL block and observe the results.

SET SERVEROUTPUT ON;

BEGIN

INSERT INTO TEST VALUES(1,’Oracle’);
INSERT INTO TEST VALUES(1,’SQLServer’);

EXCEPTION

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error Occured..’);

WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

END;
/

Above block will give following error and will not get executed.

WHEN OTHERS THEN
*
ERROR at line 8:
ORA-06550: line 8, column 4:
PLS-00370: OTHERS handler must be last among the exception handlers of a block
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated

Since WHEN OTHERS is general exception, it should be last in the exception block if more than one exception are defined in the exception block. This makes sure that any specific exception is handled correctly and appropriate actions are taken once exception is caught. Now let us change the order and execute the PL/SQL block again.

SET SERVEROUTPUT ON;
BEGIN

INSERT INTO TEST VALUES(1,’Oracle’);
INSERT INTO TEST VALUES(1,’SQLServer’);

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error..’ || SQLERRM);

END;
/

This time, second insert statement will fail and control will go to exception block. We are raising exception so it will display following error (text returned by SQLERRM)

ERROR at line 1:
ORA-20001: ORA-00001: unique constraint (DECIPHER.PK_TEST) violated
ORA-06512: at line 9

Other important thing to note is to raise an error when exception is caught using WHEN OTHERS exception. When RAISE or RAISE_APPLICATION_ERROR is used, execution stops at that point and exception is thrown back to its caller program. If RAISE is not used, then it may hide the error without stopping the execution. Following is an example.

SET SERVEROUTPUT ON;

BEGIN

INSERT INTO TEST VALUES(1,’Oracle’);
INSERT INTO TEST VALUES(NULL,’SQLServer’);

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001,SQLERRM);

WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Error..’ || SQLERRM);

END;
/

Error..ORA-01400: cannot insert NULL into (“DECIPHER”.”TEST”.”TEST_ID”)

PL/SQL procedure successfully completed.

Above example is just for illustration. Even though we have caught the exception, execution completed successfully. This holds true even if error is logged into any other table. If there is no mechanism in place to examine the errors immediately after execution, user never know that error really occurred which may be very scary especially when errors occurred due to space limitation or any other database errors. This is why using RAISE is important for WHEN OTHERS exception.

About these ads

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 77 other followers

%d bloggers like this: