Tuesday, 14 January 2014

PL/SQL - Exceptions--1

#=======================================================================
# Writer          : Lokanadham Thandlam
#=======================================================================

--Example 11–1 Setting Value of PLSQL_WARNINGS Compilation Parameter

FOR THE SESSION, ENABLE ALL warnings—highly recommended during development:
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';
FOR THE SESSION, ENABLE PERFORMANCE warnings:
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:PERFORMANCE';

--For the procedure loc_var, enable PERFORMANCE warnings, and reuse settings:
ALTER PROCEDURE loc_var;
COMPILE PLSQL_WARNINGS='ENABLE:PERFORMANCE';
REUSE SETTINGS;


--For the session, enable SEVERE warnings, disable PERFORMANCE warnings, and treat PLW-06002 warnings as errors:
ALTER SESSION;
SET PLSQL_WARNINGS='ENABLE:SEVERE', 'DISABLE:PERFORMANCE', 'ERROR:06002';
FOR THE SESSION, DISABLE ALL warnings:
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';

--Example 11–2 Displaying and Setting PLSQL_WARNINGS with DBMS_WARNING Subprograms
DISABLE ALL WARNING messages FOR this SESSION:
ALTER SESSION SET PLSQL_WARNINGS='DISABLE:ALL';
--With warnings disabled, this procedure compiles with no warnings:

CREATE OR REPLACE PROCEDURE unreachable_code
   AUTHID DEFINER
AS
   x   CONSTANT BOOLEAN := TRUE;
BEGIN
   IF x
   THEN
      DBMS_OUTPUT.PUT_LINE ('TRUE');
   ELSE
      DBMS_OUTPUT.PUT_LINE ('FALSE');
   END IF;
END unreachable_code;
/

--Enable all warning messages for this session:
CALL DBMS_WARNING.set_warning_setting_string ('ENABLE:ALL', 'SESSION');
CHECK WARNING setting:

SELECT DBMS_WARNING.get_warning_setting_string () FROM DUAL;


--Recompile procedure:
ALTER PROCEDURE unreachable_code COMPILE;


--Show errors:
SHOW ERRORS


--Example 11–3 Single Exception Handler for Multiple Exceptions

CREATE OR REPLACE PROCEDURE select_item (t_column VARCHAR2, t_name VARCHAR2)
   AUTHID DEFINER
IS
   temp   VARCHAR2 (30);
BEGIN
   temp := t_column;                 -- For error message if next SELECT fails

   -- Fails if table t_name does not have column t_column:
   SELECT COLUMN_NAME
     INTO temp
     FROM USER_TAB_COLS
    WHERE TABLE_NAME = UPPER (t_name) AND COLUMN_NAME = UPPER (t_column);

   temp := t_name;                   -- For error message if next SELECT fails

   -- Fails if there is no table named t_name:
   SELECT OBJECT_NAME
     INTO temp
     FROM USER_OBJECTS
    WHERE OBJECT_NAME = UPPER (t_name) AND OBJECT_TYPE = 'TABLE';
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp);
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Unexpected error');
      RAISE;
END;
/

--Invoke procedure (there is a DEPARTMENTS table, but it does not have a LAST_NAME column):

BEGIN
   select_item ('departments', 'last_name');
END;
/

--Invoke procedure (there is no EMP table):

BEGIN
   select_item ('emp', 'last_name');
END;
/


--Example 11–4 Locator Variables for Statements that Share Exception Handler

CREATE OR REPLACE PROCEDURE loc_var
   AUTHID DEFINER
IS
   stmt_no   POSITIVE;
   name_     VARCHAR2 (100);
BEGIN
   stmt_no := 1;

   SELECT table_name
     INTO name_
     FROM user_tables
    WHERE table_name LIKE 'ABC%';

   stmt_no := 2;

   SELECT table_name
     INTO name_
     FROM user_tables
    WHERE table_name LIKE 'XYZ%';
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      DBMS_OUTPUT.PUT_LINE ('Table name not found in query ' || stmt_no);
END;
/

CALL loc_var ();


--Example 11–5 Naming Internally Defined Exception
DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
...
EXCEPTION
WHEN deadlock_detected THEN
...
END;
/


--Example 11–6 Anonymous Block Handles ZERO_DIVIDE

DECLARE
   stock_price    NUMBER := 9.73;
   net_earnings   NUMBER := 0;
   pe_ratio       NUMBER;
BEGIN
   pe_ratio := stock_price / net_earnings;     -- raises ZERO_DIVIDE exception
   DBMS_OUTPUT.PUT_LINE ('Price/earnings ratio = ' || pe_ratio);
EXCEPTION
   WHEN ZERO_DIVIDE
   THEN
      DBMS_OUTPUT.PUT_LINE ('Company had zero earnings.');
      pe_ratio := NULL;
END;
/


--Example 11–7 Anonymous Block Avoids ZERO_DIVIDE

DECLARE
   stock_price    NUMBER := 9.73;
   net_earnings   NUMBER := 0;
   pe_ratio       NUMBER;
BEGIN
   pe_ratio :=
      CASE net_earnings WHEN 0 THEN NULL ELSE stock_price / net_earnings END;
END;
/


--Example 11–8 Anonymous Block Handles ROWTYPE_MISMATCH

CREATE OR REPLACE PACKAGE emp_dept_data
   AUTHID DEFINER
AS
   TYPE cv_type IS REF CURSOR;

   PROCEDURE open_cv (CV IN OUT cv_type, discrim IN POSITIVE);
END emp_dept_data;
/

CREATE OR REPLACE PACKAGE BODY emp_dept_data
AS
   PROCEDURE open_cv (CV IN OUT cv_type, discrim IN POSITIVE)
   IS
   BEGIN
      IF discrim = 1
      THEN
         OPEN CV FOR
              SELECT *
                FROM EMPLOYEES
            ORDER BY employee_id;
      ELSIF discrim = 2
      THEN
         OPEN CV FOR
              SELECT *
                FROM DEPARTMENTS
            ORDER BY department_id;
      END IF;
   END open_cv;
END emp_dept_data;
/


--Invoke procedure open_cv from anonymous block:

DECLARE
   emp_rec    EMPLOYEES%ROWTYPE;
   dept_rec   DEPARTMENTS%ROWTYPE;
   CV         Emp_dept_data.CV_TYPE;
BEGIN
   emp_dept_data.open_cv (CV, 1);              -- Open cv for EMPLOYEES fetch.

   FETCH CV INTO dept_rec;                          -- Fetch from DEPARTMENTS.

   DBMS_OUTPUT.PUT (dept_rec.DEPARTMENT_ID);
   DBMS_OUTPUT.PUT_LINE (' ' || dept_rec.LOCATION_ID);
EXCEPTION
   WHEN ROWTYPE_MISMATCH
   THEN
      BEGIN
         DBMS_OUTPUT.PUT_LINE (
            'Row type mismatch, fetching EMPLOYEES data ...');

         FETCH CV INTO emp_rec;

         DBMS_OUTPUT.PUT (emp_rec.DEPARTMENT_ID);
         DBMS_OUTPUT.PUT_LINE (' ' || emp_rec.LAST_NAME);
      END;
END;
/

--
--Example 11–9 Redeclared Predefined Identifier
DROP TABLE t;

CREATE TABLE t (c NUMBER);

--In the following block, the INSERT statement implicitly raises the predefined exception INVALID_NUMBER, which the exception handler handles.

DECLARE
   default_number   NUMBER := 0;
BEGIN
   INSERT INTO t
        VALUES (TO_NUMBER ('100.00', '9G999'));
EXCEPTION
   WHEN INVALID_NUMBER
   THEN
      DBMS_OUTPUT.PUT_LINE ('Substituting default value for invalid number.');

      INSERT INTO t
           VALUES (default_number);
END;
/


--The following block redeclares the predefined exception INVALID_NUMBER. When the INSERT statement implicitly raises the predefined exception INVALID_NUMBER, the exception handler does not handle it.

DECLARE
   default_number   NUMBER := 0;
   i                NUMBER := 5;
   INVALID_NUMBER   EXCEPTION;               -- redeclare predefined exception
BEGIN
   INSERT INTO t
        VALUES (TO_NUMBER ('100.00', '9G999'));
EXCEPTION
   WHEN INVALID_NUMBER
   THEN
      DBMS_OUTPUT.PUT_LINE ('Substituting default value for invalid number.');

      INSERT INTO t
           VALUES (default_number);
END;
/



--The exception handler in the preceding block handles the predefined exception INVALID_NUMBER if you qualify the exception name in the exception handler:

DECLARE
   default_number   NUMBER := 0;
   i                NUMBER := 5;
   INVALID_NUMBER   EXCEPTION;               -- redeclare predefined exception
BEGIN
   INSERT INTO t
        VALUES (TO_NUMBER ('100.00', '9G999'));
EXCEPTION
   WHEN STANDARD.INVALID_NUMBER
   THEN
      DBMS_OUTPUT.PUT_LINE ('Substituting default value for invalid number.');

      INSERT INTO t
           VALUES (default_number);
END;
/


--Example 11–10 Declaring, Raising, and Handling User-Defined Exception

CREATE PROCEDURE account_status (due_date DATE, today DATE)
   AUTHID DEFINER
IS
   past_due   EXCEPTION;                                  -- declare exception
BEGIN
   IF due_date < today
   THEN
      RAISE past_due;                            -- explicitly raise exception
   END IF;
EXCEPTION
   WHEN past_due
   THEN                                                    -- handle exception
      DBMS_OUTPUT.PUT_LINE ('Account past due.');
END;
/

BEGIN
   account_status (TO_DATE ('01-JUL-2010', 'DD-MON-YYYY'),
                   TO_DATE ('09-JUL-2010', 'DD-MON-YYYY'));
END;
/


--Example 11–11 Explicitly Raising Predefined Exception
DROP TABLE t;

CREATE TABLE t (c NUMBER);

CREATE PROCEDURE p (n NUMBER)
   AUTHID DEFINER
IS
   default_number   NUMBER := 0;
BEGIN
   IF n < 0
   THEN
      RAISE INVALID_NUMBER;                                -- raise explicitly
   ELSE
      INSERT INTO t
           VALUES (TO_NUMBER ('100.00', '9G999'));         -- raise implicitly
   END IF;
EXCEPTION
   WHEN INVALID_NUMBER
   THEN
      DBMS_OUTPUT.PUT_LINE ('Substituting default value for invalid number.');

      INSERT INTO t
           VALUES (default_number);
END;
/


BEGIN
   p (-1);
END;
/


BEGIN
   p (1);
END;
/


--Example 11–12 Reraising Exception

DECLARE
   salary_too_high    EXCEPTION;
   current_salary     NUMBER := 20000;
   max_salary         NUMBER := 10000;
   erroneous_salary   NUMBER;
BEGIN
   BEGIN
      IF current_salary > max_salary
      THEN
         RAISE salary_too_high;                             -- raise exception
      END IF;
   EXCEPTION
      WHEN salary_too_high
      THEN                                         -- start handling exception
         erroneous_salary := current_salary;
         DBMS_OUTPUT.PUT_LINE (
            'Salary ' || erroneous_salary || ' is out of range.');
         DBMS_OUTPUT.PUT_LINE ('Maximum salary is ' || max_salary || '.');
         RAISE;      -- reraise current exception (exception name is optional)
   END;
EXCEPTION
   WHEN salary_too_high
   THEN                                           -- finish handling exception
      current_salary := max_salary;
      DBMS_OUTPUT.PUT_LINE (
            'Revising salary from '
         || erroneous_salary
         || ' to '
         || current_salary
         || '.');
END;
/

--Example 11–13 Raising User-Defined Exception with RAISE_APPLICATION_ERROR

CREATE PROCEDURE account_status (due_date DATE, today DATE)
   AUTHID DEFINER
IS
BEGIN
   IF due_date < today
   THEN                                          -- explicitly raise exception
      RAISE_APPLICATION_ERROR (-20000, 'Account past due.');
   END IF;
END;
/

DECLARE
   past_due   EXCEPTION;                                  -- declare exception
   PRAGMA EXCEPTION_INIT (past_due, -20000); -- assign error code to exception
BEGIN
   account_status (TO_DATE ('01-JUL-2010', 'DD-MON-YYYY'),
                   TO_DATE ('09-JUL-2010', 'DD-MON-YYYY')); -- invoke procedure
EXCEPTION
   WHEN past_due
   THEN                                                    -- handle exception
      DBMS_OUTPUT.PUT_LINE (TO_CHAR (SQLERRM (-20000)));
END;
/

                





Posted by at
Labels: ,