Exception in PL/SQL

Exception

Exception is a PL/SQL error that is raised during program execution.

Example of Exception

DECLARE
v_lname VARCHAR2(15);
BEGIN
SELECT last_name INTO v_lname
FROM employees
WHERE first_name='John';
DBMS_OUTPUT.PUT_LINE ('John ''s last name is :'||v_lname);
END;

Here query Returning Multiple rows but variable can hold single value at a time.

How to overcome this type of EXCEPTION


By writing EXCEPTION Section in Program

Example:

DECLARE
v_lname VARCHAR2 (15);
BEGIN
SELECT last_name INTO v_lname
FROM employees
WHERE first_name='John';
DBMS_OUTPUT.PUT_LINE ('John''s last name is : '||v_lname);
EXCEPTION
WHEN TOO_MANY_ROWS then
dbms_output.put_line('Query is returning more than one rows');
END;

Output:

Handling Exceptions with PL/SQL


–An exception is a PL/SQL error that is raised during program execution.

An exception can be raised:


  1. Implicitly by the Oracle server
  2. Explicitly by the program

An exception can be handled:


  • By trapping it with a handler
  • By propagating it to the calling environment

Types of Exception


  • Predefined Oracle server
  • Non-predefined Oracle server
  • User-defined

Sample predefined exceptions:


  • NO_DATA_FOUND
  • TOO_MANY_ROWS
  • INVALID_CURSOR
  • ZERO_DIVIDE
  • DUP_VAL_ON_INDEX

Non-Predefined Error


DECLARE
e_insert_excep EXCEPTION;
PRAGMA EXCEPTION_INIT(e_insert_excep, -01400);
BEGIN
INSERT INTO departments (department_id, department_name) VALUES (100, NULL);
EXCEPTION
WHEN e_insert_excep THEN
DBMS_OUTPUT.PUT_LINE('INSERT OPERATION FAILED');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

Functions for Trapping Exceptions


SQLCODE: Returns the numeric value for the error code.

SQLERRM: Returns the message associated with the error number.

Example of User-defined Exception:

DECLARE
v_deptno NUMBER := 200;
v_name VARCHAR2(20) := 'dev';
e_invalid_department EXCEPTION;
BEGIN
UPDATE departments
SET department_name = v_name
WHERE department_id = v_deptno;
IF SQL % NOTFOUND THEN
RAISE e_invalid_department;
END IF;
COMMIT;
EXCEPTION
WHEN e_invalid_department THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;

RAISE_APPLICATION_ERROR Procedure


Used in two different places:

  1. Executable section
  2. Exception section

–Returns error conditions to the user in a manner consistent with other Oracle server errors

RAISE_APPLICATION_ERROR Procedure

  1. Executable section:
  2. Exception section:
DECLARE
age INTEGER :=17 ;
BEGIN
IF (age < 18) THEN
Raise_Application_Error (-20555, 'candidate is not eligible for voting...');
END IF;
END;

Output:

PL/SQL package

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms.

Packages usually have two parts:


  1. A specification (spec)
  2. A body
  • The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package.
  • A package always has a specification, which declares the public items that can be referenced from outside the package.
  • The body defines the queries for the cursors and the code for the subprograms (Function and Procedure).
  • Enable the Oracle server to read multiple objects into memory at once.
  • You can map a package subprogram to an external Java or C subprogram by using a call specification

Advantages of Using Packages


  • Modularity : Encapsulating related constructs
  • Easier maintenance: Keeping logically related functionality together
  • Better Performance: Oracle load whole package into memory. if you change the body of a package function, Oracle Database does not recompile other subprograms that invoke the function, because these subprograms depend only on the parameters and return value that are declared in the specification.
  • Easier to Grant Roles: You can grant roles on the package, instead of granting roles on each object in the package.
  • Easier application design: Coding and compiling the specification and body separately
  • Hiding information: Only the declarations in the package specification are visible and accessible to applications
  • Private constructs in the package body are hidden and inaccessible
  • All coding is hidden in the package body

Creating package


CREATE OR REPLACE
PACKAGE pkg_sal IS
v_sal NUMBER;
PROCEDURE get_max_sal;
END pkg_sal;

Creating package body


create or replace package body pkg_sal 
is
procedure get_max_sal is
begin
select max(salary) into v_sal from employees;
dbms_output.put_line('Maximum Salary '|| v_sal);
end get_max_sal;
end pkg_sal;

Calling package

begin
pkg_sal.get_max_sal;
end;

Output:

Creating and Using Body less Packages


CREATE OR REPLACE PACKAGE pkg_area IS
c_pie CONSTANT NUMBER := 3.14;
c_km CONSTANT NUMBER := 1000;
END pkg_area;
DECLARE
v_area_circle NUMBER;
v_radius NUMBER :=5;
BEGIN
v_area_circle:= pkg_area.c_pie * v_radius;
dbms_output.put_line('area of circle '||v_area_circle);
END;

Output:

Remove the package specification and body

DROP PACKAGE package_name;

Remove the package body only

DROP PACKAGE BODY package_name;

Viewing Packages Using the Data Dictionary


View the package specification.

SELECT text
FROM user_source
WHERE name = 'COMM_PKG' AND type = 'PACKAGE'
ORDER BY LINE;

View the package body.

SELECT text
FROM user_source
WHERE name = 'COMM_PKG' AND type = 'PACKAGE BODY'
ORDER BY LINE;

Reference Link: https://docs.oracle.com/database/121/LNPLS/packages.htm#LNPLS99924