Why is this an issue?
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;
Resources
