Home

Description

The WHEN OTHERS clauses should be used for exception handling in PL/SQL code. This clause is used to catch any exceptions that are not explicitly handled by the code. It is important to use this clause as it allows the code to handle unexpected errors and prevents the code from crashing. This clause should be used in conjunction with other exception handling techniques such as using the RAISE statement and using the EXCEPTION_INIT pragma. This clause should also be used to log any errors that occur during execution of the code.

Key Benefits

• Efficient Error Handling: WHEN OTHERS clauses provide an efficient way to handle errors that are not explicitly handled by the program.
• Error Logging: WHEN OTHERS clauses can be used to log errors that occur in the program, allowing for better debugging and troubleshooting.
• Reduced Code Complexity: WHEN OTHERS clauses help to reduce code complexity by providing a single point of error handling for multiple errors.

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 ERRORS THEN          --Non compliant code (WHEN OTHERS clauses is not 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 clauses is used for exception handling)
        error_message := SQLERRORMSG;
        DBMS_OUTPUT.PUT_LINE (error_message);		   
        RETURN;
END CUSTOMER_TABLE_ITERATION;