Exception Handling

In some of the project which I was supporting, I faced lot of problems with the errors and the way the error messages were displayed. Most of the programs didn't had an exception handler, even if some programs had exception handler, the error message was not useful. So, I thought of sharing my experience and how exception handler can help the programmers in debugging the application.

Version 1:

CREATE OR REPLACE PROCEDURE pr_emp ( p_column_x employees.column_x%TYPE , p_column_y employees.column_y%TYPE , p_column_z employees.column_z%TYPE) IS v_column_a employees.column_a%TYPE; v_column_b employees.column_b%TYPE v_column_c employees.column_c%TYPE; v_column_x departments.column_x%TYPE; c_commission CONSTANT NUMBER(2):=10; BEGIN SELECT column_a INTO v_column_a FROM employees WHERE column_x = p_column_x AND column_y = p_column_y; --Some more statements will go here... ... ... ... INSERT INTO table_a;... UPDATE table_b SET ... WHERE condition; IF SQL%ROWCOUNT = 0 THEN SELECT column_c INTO v_column_c FROM employees WHERE column_z = p_column_z; INSERT INTO table_a ... END IF; END pr_emp;

When the procedure is executed, if the program raises an exception, then the program will be terminated with un-handled exception. If the procedure is called from another program then, the calling program also terminated. Suppose, NO_DATA_FOUND exception is raised, then the procedure execution will be stopped and the following message will be displayed:


ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "SCOTT.PR_EMP ", line 7
ORA-06512: at line 1


Same program with exception will allow the calling program to be executed normally (if the exception handler does not have RAISE_APPLICATION_ERROR in it. We will not use RAISE_APPLICATION_ERROR here; instead we will use DBMS_OUTPUT.PUT_LINE for displaying the error.


Version 2:

CREATE OR REPLACE PROCEDURE pr_emp ( p_column_x employees.column_x%TYPE , p_column_y employees.column_y%TYPE , p_column_z employees.column_z%TYPE) IS v_column_a employees.column_a%TYPE; v_column_b employees.column_b%TYPE v_column_c employees.column_c%TYPE; v_column_x departments.column_x%TYPE; c_commission CONSTANT NUMBER(2):=10; BEGIN SELECT column_a INTO v_column_a FROM employees WHERE column_x = p_column_x AND column_y = p_column_y; --Some more statements will go here... ... ... ... INSERT INTO table_a;... UPDATE table_b SET ... WHERE condition; IF SQL%ROWCOUNT = 0 THEN SELECT column_c INTO v_column_c FROM employees WHERE column_z = p_column_z; INSERT INTO table_a ... END IF; EXCEPTION WHEN OTHERS THEN --The exception can be logged into a message table -- or can be displayed by using DBMS_OUTPUT.PUT_LINE --Here we will use DBMS_OUTPUT package DBMS_OUTPUT.PUT_LINE(‘Error occurred in the procedure and the error is ‘||SQLERRM); END pr_emp;

This will display the error message that occurred is the procedure. Suppose, the program raise a NO_DATA_FOUND exception, then program execution will be stopped and the following message will be displayed:

ORA-01403: no data found

By looking at the message we can say that the first message, which was displayed, is better than this message as the first message at least gives us the line no where which caused the exception.

Version 1 will not allow the execution of the calling program and the second one will allow. But the error message is not informative.

Let us change the code to get the line no also

Version 3:

CREATE OR REPLACE PROCEDURE pr_emp ( p_column_x employees.column_x%TYPE , p_column_y employees.column_y%TYPE , p_column_z employees.column_z%TYPE) IS v_column_a employees.column_a%TYPE; v_column_b employees.column_b%TYPE v_column_c employees.column_c%TYPE; v_column_x departments.column_x%TYPE; c_commission CONSTANT NUMBER(2):=10; --Declare a variable to hold the statement no v_line_no PLS_INTEGER; BEGIN v_line_no := 10; SELECT column_a INTO v_column_a FROM employees WHERE column_x = p_column_x AND column_y = p_column_y; --Some more statements will go here... ... ... ... v_line_no := 100; INSERT INTO table_a;... v_line_no := 110; UPDATE table_b SET ... WHERE condition; IF SQL%ROWCOUNT = 0 THEN v_line_no := 120; SELECT column_c INTO v_column_c FROM employees WHERE column_z = p_column_z; v_line_no := 130; INSERT INTO table_a ... END IF; EXCEPTION WHEN OTHERS THEN --The exception can be logged into a message table -- or can be displayed by using DBMS_OUTPUT.PUT_LINE --Here we will use DBMS_OUTPUT package DBMS_OUTPUT.PUT_LINE(‘Error occurred in the procedure and the error is ‘||SQLERRM ||' at line no '|| v_line_no); END pr_emp;

Now, if the program is executed, and the select statement raised the first NO_DATA_FOUND exception, then the following message will be displayed

ORA-01403: no data found at line no 10

If the procedure is called directly by passing the parameters then, as a developer of the program you know that, the values that were passe
d to the program is not returning any data from the employees table.

For others, they have to open the source and see the select statement to know the table name that is not returning the data.

Let’s change the procedure again.

Version 4:

CREATE OR REPLACE PROCEDURE pr_emp ( p_column_x employees.column_x%TYPE , p_column_y employees.column_y%TYPE , p_column_z employees.column_z%TYPE) IS v_column_a employees.column_a%TYPE; v_column_b employees.column_b%TYPE v_column_c employees.column_c%TYPE; v_column_x departments.column_x%TYPE; c_commission CONSTANT NUMBER(2):=10; --Along with the statement no, we will try to get the table/statement also. --We use a character variable instead of numeric variable v_stmt VARCHAR2(32000); --Declare a variable to hold the statement no --v_line_no PLS_INTEGER; BEGIN v_stmt := 'Select stmt on employees table at 10'; SELECT column_a INTO v_column_a FROM employees WHERE column_x = p_column_x AND column_y = p_column_y; --Some more statements will go here... ... ... ... v_stmt := 'Insert into table_a at 100'; INSERT INTO table_a;... v_stmt := 'Update stmt on table_a at 110'; UPDATE table_b SET ... WHERE condition; IF SQL%ROWCOUNT = 0 THEN v_stmt := 'Select stmt on employees table at 120'; SELECT column_c INTO v_column_c FROM employees WHERE column_z = p_column_z; v_stmt := 'Insert into table_a at 130'; INSERT INTO table_a ... END IF; EXCEPTION WHEN OTHERS THEN --The exception can be logged into a message table -- or can be displayed by using DBMS_OUTPUT.PUT_LINE --Here we will use DBMS_OUTPUT package DBMS_OUTPUT.PUT_LINE(‘Error occurred in the procedure and the error is ‘||SQLERRM ||' at stmt = '|| v_stmt); END pr_emp;

When this procedure is executed with the same parameter, then the error will be

ORA-01403: no data found at Select stmt on employees table at 10

This implies that, the select statement on employees table is not returning values. Again, as a developer you know that the table may not have any data for the values passed as parameters. Even other developers, who are going to execute can understand the error.

In the above code, it’s better to have the line no along with the statement. This is because; if the line no is not used in the v_stmt and the same NO_DATA_FOUND exception is raised, then how can you find out which select statement on employees raised the exception. We have two select statements on employee tables viz, at line 10 and at line 120. So, have the line no v_stmt will help provide some more information.

Now, lets consider another scenario. Suppose, this procedure is called from another program and the same NO_DATA_FOUND exception is raised. Because the procedure is called from another program, we don’t know what are the values passed to the procedure. How debug now?

Lets re-write the code

Version 5:

CREATE OR REPLACE PROCEDURE pr_emp ( p_column_x employees.column_x%TYPE , p_column_y employees.column_y%TYPE , p_column_z employees.column_z%TYPE) IS v_column_a employees.column_a%TYPE; v_column_b employees.column_b%TYPE v_column_c employees.column_c%TYPE; v_column_x departments.column_x%TYPE; c_commission CONSTANT NUMBER(2):=10; --Along with the statement no, we will try to get the table/statement also. --We use a character variable instead of numeric variable v_stmt VARCHAR2(32000); --Declare a variable to hold the statement no --v_line_no PLS_INTEGER; BEGIN v_stmt := 'Select stmt on employees where column_x = ' || p_column_x || ' AND column_y = ' || p_column_y || ' at 10'; SELECT column_a INTO v_column_a FROM employees WHERE column_x = p_column_x AND column_y = p_column_y; --Some more statements will go here... ... ... ... v_stmt := 'Insert into table_a at 100'; INSERT INTO table_a;... v_stmt := 'Update stmt on table_b where x = '|| v_column_a ||' at 110'; UPDATE table_b SET ... WHERE condition; IF SQL%ROWCOUNT = 0 THEN v_stmt := 'Select stmt on employees where column_z = '|| p_column_z||' at 120'; SELECT column_c INTO v_column_c FROM employees WHERE column_z = p_column_z; v_stmt := 'Insert into table_a at 130'; INSERT INTO table_a ... END IF; EXCEPTION WHEN OTHERS THEN --The exception can be logged into a message table -- or can be displayed by using DBMS_OUTPUT.PUT_LINE --Here we will use DBMS_OUTPUT package DBMS_OUTPUT.PUT_LINE(‘Error occurred in the procedure and the error is ‘||SQLERRM ||' at stmt = '|| v_stmt); END pr_emp;

For the same NO_DATA_FOUND exception, the following message will be displayed

ORA-01403: no data found at Select stmt on employees where column_x = 100 AND column_y = 150 at 10

Assuming that, p_column_x = 100 and p_column_y = 150 while calling procedure

So, you can find out the where condition which caused the select statement to fail. This type of messaging will help in finding out any other exception that was raised then by any statement

Last, but not the least, why the numbers were given in multiples of 10s. This is because, suppose statements were numbered like 1, 2, 3 etc and the program needs to be modified and more statements needs to be added in between the line 2 and 3 then how to number the statements. On the other hand, if the statements were numbered like 10, 20, 30 etc, then the added statements between 20 and 30 can be numbered as 12, 14 etc.

Those who worked in BASIC language can relate this.

All the statements need not to be numbered. Use v_stmt only if an exception is expected and needs to be handled.