Details

    • Type: Language-Specification
    • Status: Active
    • Resolution: Unresolved
    • Message:
      Add exception handlers for the expected exception types.
    • Default Severity:
      Critical
    • Impact:
      High
    • Likelihood:
      Low
    • Legacy Key:
      WhenOthersAsOnlyExceptionHandlerCheck
    • Remediation Function:
      Constant/Issue
    • Constant Cost:
      1h

      Description

      Before trapping all possible exceptions, it is best to try to trap the specific ones and try to recover from those.

      Noncompliant Code Example

      SET SERVEROUTPUT ON
      
      CREATE TABLE hitCounter
      (
        page VARCHAR2(42),
        hits NUMBER,
        CONSTRAINT pk PRIMARY KEY (page)
      );
      
      CREATE PROCEDURE hitPage(pageIn VARCHAR2) AS
      BEGIN
        INSERT INTO hitCounter VALUES (pageIn, 1);
      EXCEPTION -- Noncompliant, the only exception handler is WHEN OTHERS
        WHEN OTHERS THEN
          IF SQLCODE = -1 THEN
            UPDATE hitCounter SET hits = hits + 1 WHERE page = pageIn;
          ELSE
            DBMS_OUTPUT.PUT_LINE('An unknown error occured!');
          END IF;
      END;
      /
      
      BEGIN
        hitPage('index.html');
        hitPage('index.html');
      END;
      /
      
      SELECT * FROM hitCounter;
      
      DROP PROCEDURE hitPage;
      DROP TABLE hitCounter;
      

      Compliant Solution

      SET SERVEROUTPUT ON
      
      CREATE TABLE hitCounter
      (
        page VARCHAR2(42),
        hits NUMBER,
        CONSTRAINT pk PRIMARY KEY (page)
      );
      
      CREATE PROCEDURE hitPage(pageIn VARCHAR2) AS
      BEGIN
        INSERT INTO hitCounter VALUES (pageIn, 1);
      EXCEPTION
        WHEN DUP_VAL_ON_INDEX THEN
          UPDATE hitCounter SET hits = hits + 1 WHERE page = pageIn;
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('An unknown error occured!');
      END;
      /
      
      BEGIN
        hitPage('index.html');
        hitPage('index.html');
      END;
      /
      
      SELECT * FROM hitCounter;
      
      DROP PROCEDURE hitPage;
      DROP TABLE hitCounter;
      

        Attachments

          Activity

            People

            Assignee:
            Unassigned Unassigned
            Reporter:
            ann.campbell.2 Ann Campbell
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated: