Monday, 19 May 2014

PL/SQL - Exceptions--2

--Example 11–14 Exception that Propagates Beyond Scope is Handled
CREATE OR REPLACE PROCEDURE p
   AUTHID DEFINER
AS
BEGIN
   DECLARE
      past_due      EXCEPTION;
      PRAGMA EXCEPTION_INIT (past_due, -4910);
      due_date      DATE := TRUNC (SYSDATE) - 1;
      todays_date   DATE := TRUNC (SYSDATE);
   BEGIN
      IF due_date < todays_date
      THEN
         RAISE past_due;
      END IF;
   END;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      RAISE;
END;
/


--Example 11–15 Exception that Propagates Beyond Scope is Not Handled

BEGIN
   DECLARE
      past_due      EXCEPTION;
      due_date      DATE := TRUNC (SYSDATE) - 1;
      todays_date   DATE := TRUNC (SYSDATE);
   BEGIN
      IF due_date < todays_date
      THEN
         RAISE past_due;
      END IF;
   END;
END;
/


--Example 11–16 Exception Raised in Declaration is Not Handled

DECLARE
   credit_limit   CONSTANT NUMBER (3) := 5000;         -- Maximum value is 999
BEGIN
   NULL;
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.PUT_LINE ('Exception raised in declaration.');
END;
/


--Example 11–17 Exception Raised in Declaration is Handled by Enclosing Block

BEGIN
   DECLARE
      credit_limit   CONSTANT NUMBER (3) := 5000;
   BEGIN
      NULL;
   END;
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      DBMS_OUTPUT.PUT_LINE ('Exception raised in declaration.');
END;
/


--Example 11–18 Exception Raised in Exception Handler is Not Handled

CREATE PROCEDURE print_reciprocal (n NUMBER)
   AUTHID DEFINER
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE (1 / n);                                    -- handled
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error:');
      DBMS_OUTPUT.PUT_LINE (1 / n || ' is undefined');          -- not handled
END;
/

BEGIN                                                        -- invoking block
   print_reciprocal (0);
END;



--Example 11–18 Exception Raised in Exception Handler is Not Handled

CREATE PROCEDURE print_reciprocal (n NUMBER)
   AUTHID DEFINER
IS
BEGIN
   DBMS_OUTPUT.PUT_LINE (1 / n);                                    -- handled
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error:');
      DBMS_OUTPUT.PUT_LINE (1 / n || ' is undefined');          -- not handled
END;
/

BEGIN                                                        -- invoking block
   print_reciprocal (0);
END;


--Example 11–20 Exception Raised in Exception Handler is Handled by Enclosing Block

CREATE PROCEDURE print_reciprocal (n NUMBER)
   AUTHID DEFINER
IS
BEGIN
   BEGIN
      DBMS_OUTPUT.PUT_LINE (1 / n);
   EXCEPTION
      WHEN ZERO_DIVIDE
      THEN
         DBMS_OUTPUT.PUT_LINE ('Error in inner block:');
         DBMS_OUTPUT.PUT_LINE (1 / n || ' is undefined.');
   END;
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN                       -- handles exception raised in exception handler
      DBMS_OUTPUT.PUT ('Error in outer block: ');
      DBMS_OUTPUT.PUT_LINE ('1/0 is undefined.');
END;
/

BEGIN
   print_reciprocal (0);
END;
/


--Example 11–21 Exception Raised in Exception Handler is Not Handled

CREATE PROCEDURE descending_reciprocals (n INTEGER)
   AUTHID DEFINER
IS
   i          INTEGER;
   i_is_one   EXCEPTION;
BEGIN
   i := n;

   LOOP
      IF i = 1
      THEN
         RAISE i_is_one;
      ELSE
         DBMS_OUTPUT.PUT_LINE ('Reciprocal of ' || i || ' is ' || 1 / i);
      END IF;

      i := i - 1;
   END LOOP;
EXCEPTION
   WHEN i_is_one
   THEN
      DBMS_OUTPUT.PUT_LINE ('1 is its own reciprocal.');
      DBMS_OUTPUT.PUT_LINE (
            'Reciprocal of '
         || TO_CHAR (i - 1)
         || ' is '
         || TO_CHAR (1 / (i - 1)));
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.PUT_LINE ('Error:');
      DBMS_OUTPUT.PUT_LINE (1 / n || ' is undefined');
END;
/

BEGIN
   descending_reciprocals (3);
END;
/


--Example 11–22 Exception Raised in Exception Handler is Handled by Enclosing Block

CREATE PROCEDURE descending_reciprocals (n INTEGER)
   AUTHID DEFINER
IS
   i          INTEGER;
   i_is_one   EXCEPTION;
BEGIN
   BEGIN
      i := n;

      LOOP
         IF i = 1
         THEN
            RAISE i_is_one;
         ELSE
            DBMS_OUTPUT.PUT_LINE ('Reciprocal of ' || i || ' is ' || 1 / i);
         END IF;

         i := i - 1;
      END LOOP;
   EXCEPTION
      WHEN i_is_one
      THEN
         DBMS_OUTPUT.PUT_LINE ('1 is its own reciprocal.');
         DBMS_OUTPUT.PUT_LINE (
               'Reciprocal of '
            || TO_CHAR (i - 1)
            || ' is '
            || TO_CHAR (1 / (i - 1)));
      WHEN ZERO_DIVIDE
      THEN
         DBMS_OUTPUT.PUT_LINE ('Error:');
         DBMS_OUTPUT.PUT_LINE (1 / n || ' is undefined');
   END;
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN                       -- handles exception raised in exception handler
      DBMS_OUTPUT.PUT_LINE ('Error:');
      DBMS_OUTPUT.PUT_LINE ('1/0 is undefined');
END;
/


BEGIN
   descending_reciprocals (3);
END;
/


--Example 11–23 Displaying SQLCODE and SQLERRM Values
DROP TABLE errors;

CREATE TABLE errors
(
   code      NUMBER,
   MESSAGE   VARCHAR2 (64)
);

CREATE OR REPLACE PROCEDURE p
   AUTHID DEFINER
AS
   name     EMPLOYEES.LAST_NAME%TYPE;
   v_code   NUMBER;
   v_errm   VARCHAR2 (64);
BEGIN
   SELECT last_name
     INTO name
     FROM EMPLOYEES
    WHERE EMPLOYEE_ID = -1;
EXCEPTION
   WHEN OTHERS
   THEN
      v_code := SQLCODE;
      v_errm := SUBSTR (SQLERRM, 1, 64);
      DBMS_OUTPUT.PUT_LINE ('Error code ' || v_code || ': ' || v_errm);

      /* Invoke another procedure,
      declared with PRAGMA AUTONOMOUS_TRANSACTION,
      to insert information about errors. */
      INSERT INTO errors (code, MESSAGE)
           VALUES (v_code, v_errm);

      RAISE;
END;
/


--Example 11–24 Exception Handler Runs and Execution Ends
DROP TABLE employees_temp;

CREATE TABLE employees_temp
AS
   SELECT employee_id, salary, commission_pct FROM employees;

DECLARE
   sal_calc   NUMBER (8, 2);
BEGIN
   INSERT INTO employees_temp (employee_id, salary, commission_pct)
        VALUES (301, 2500, 0);

   SELECT (salary / commission_pct)
     INTO sal_calc
     FROM employees_temp
    WHERE employee_id = 301;

   INSERT INTO employees_temp
        VALUES (302, sal_calc / 100, .1);

   DBMS_OUTPUT.PUT_LINE ('Row inserted.');
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.PUT_LINE ('Division by zero.');
END;
/


--Example 11–25 Exception Handler Runs and Execution Continues

DECLARE
   sal_calc   NUMBER (8, 2);
BEGIN
   INSERT INTO employees_temp (employee_id, salary, commission_pct)
        VALUES (301, 2500, 0);

   BEGIN
      SELECT (salary / commission_pct)
        INTO sal_calc
        FROM employees_temp
       WHERE employee_id = 301;
   EXCEPTION
      WHEN ZERO_DIVIDE
      THEN
         DBMS_OUTPUT.PUT_LINE ('Substituting 2500 for undefined number.');
         sal_calc := 2500;
   END;

   INSERT INTO employees_temp
        VALUES (302, sal_calc / 100, .1);

   DBMS_OUTPUT.PUT_LINE ('Enclosing block: Row inserted.');
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.PUT_LINE ('Enclosing block: Division by zero.');
END;
/


--Example 11–26 Retrying Transaction After Handling Exception
DROP TABLE results;

CREATE TABLE results
(
   res_name     VARCHAR (20),
   res_answer   VARCHAR2 (3)
);

CREATE UNIQUE INDEX res_name_ix
   ON results (res_name);

INSERT INTO results (res_name, res_answer)
     VALUES ('SMYTHE', 'YES');

INSERT INTO results (res_name, res_answer)
     VALUES ('JONES', 'NO');

DECLARE
   name     VARCHAR2 (20) := 'SMYTHE';
   answer   VARCHAR2 (3) := 'NO';
   suffix   NUMBER := 1;
BEGIN
   FOR i IN 1 .. 5
   LOOP                                    -- Try transaction at most 5 times.
      DBMS_OUTPUT.PUT ('Try #' || i);

      BEGIN                                                -- sub-block begins
         SAVEPOINT start_transaction;

         -- transaction begins
         DELETE FROM results
               WHERE res_answer = 'NO';

         INSERT INTO results (res_name, res_answer)
              VALUES (name, answer);

         -- Nonunique name raises DUP_VAL_ON_INDEX.
         -- If transaction succeeded:
         COMMIT;
         DBMS_OUTPUT.PUT_LINE (' succeeded.');
         EXIT;
      EXCEPTION
         WHEN DUP_VAL_ON_INDEX
         THEN
            DBMS_OUTPUT.PUT_LINE (' failed; trying again.');
            ROLLBACK TO start_transaction;                    -- Undo changes.
            suffix := suffix + 1;                       -- Try to fix problem.
            name := name || TO_CHAR (suffix);
      END;                                                   -- sub-block ends
   END LOOP;
END;

/
Posted by at
Labels: ,