Home

Description

The rule "WHEN OTHERS should not be the only exception handler" states that when writing PL/SQL code, you should not rely solely on the WHEN OTHERS exception handler. Instead, you should create specific exception handlers for each exception that you anticipate. This will allow you to handle each exception in a specific way, rather than relying on a generic exception handler. This will also make your code more efficient, as it will not have to process each exception in the same way. By creating specific exception handlers, you can also provide more meaningful error messages to the user, which will help them to understand and fix the issue more quickly.

Key Benefits

• Error Handling: WHEN OTHERS should not be the only exception handler rule provides better error handling, as it allows developers to identify and address potential errors more quickly.
• Improved Performance: Using WHEN OTHERS should not be the only exception handler rule can improve the performance of the code, as it allows developers to identify and address potential errors more quickly.
• Reduced Maintenance: Using WHEN OTHERS should not be the only exception handler rule can reduce the amount of maintenance required, as it allows developers to identify and address potential errors more quickly.

Non-compliant Code Example

BEGIN
	FOR i IN 1 .. CUSTOMER_TABLE.COUNT  
		LOOP	
			EXIT WHEN CUSTOMER_TABLE(i).Id > 25; 
			IF CUSTOMER_TABLE(i) IS NOT NULL THEN
				DBMS_OUTPUT.PUT( i || ' = (' || CUSTOMER_TABLE(i).Name || ', ' || CUSTOMER_TABLE(i).PhoneNumber || ')' );
			END IF;
			
		END LOOP;
EXCEPTION
    WHEN OTHERS THEN          --Non compliant code (WHEN OTHERS clause only used for exception handling)
        error_message := SQLERRORMSG;
        DBMS_OUTPUT.PUT_LINE (error_message);		   
        RETURN;
END CUSTOMER_TABLE_ITERATION;

Compliant Code Example

BEGIN
	FOR i IN 1 .. CUSTOMER_TABLE.COUNT  
		LOOP	
			EXIT WHEN CUSTOMER_TABLE(i).Id > 25; 
			IF CUSTOMER_TABLE(i) IS NOT NULL THEN
				DBMS_OUTPUT.PUT( i || ' = (' || CUSTOMER_TABLE(i).Name || ', ' || CUSTOMER_TABLE(i).PhoneNumber || ')' );
			END IF;
			
		END LOOP;
EXCEPTION
    WHEN ERRORS THEN          
        error_message := SQLERRORMSG;
        DBMS_OUTPUT.PUT_LINE (error_message);	    
        RETURN;
    WHEN OTHERS THEN          --Compliant code (WHEN OTHERS clause is used along with WHEN ERRORS clause  for exception handling)
        error_message := SQLERRORMSG;
        DBMS_OUTPUT.PUT_LINE (error_message);		   
        RETURN;
END CUSTOMER_TABLE_ITERATION;