bharti vidyapeeths jawaharlal Nehru institute of Technology

MICRO-PROJECT

Topic – PL/SQL program based on Exception handling

Program- Computer Technology
Course- Database management system(DBMS)
Semister- 3 I-scheme
Academic Year- 2021-2022
Faculti- Mrs. Siara A Mujawar
Submitted by – Pranav satarkar, Mohit Tade, Devansh
Rajput, Antriksh Shinde
Roll no- 36,46,29,40
MIN

Exception Handling in PL/SQL

An exception is an error which disrupts the normal flow of program instructions.
An exception is an error condition during a program execution. PL/SQL supports
programmers to catch such conditions using EXCEPTION block in the program and an
appropriate action is taken against the error condition. There are two types of exceptions
−

 System-defined exceptions
 User-defined exceptions

Syntax to write an exception

WHEN exception THEN
statement;

Structure of Exception Handling

DECLARE
declarations section;
BEGIN
executable command(s);
EXCEPTION
WHEN exception1 THEN
statement1;
WHEN exception2 THEN
statement2;
[WHEN others THEN]
/* default exception handling code */
END;

2
Note:
When other keyword should be used only at the end of the exception handling block as
no exception handling part present later will get executed as the control will exit from
the block after executing the WHEN OTHERS.

DECLARE
c_id customers.id%type := 8;
c_name customerS.Name%type;
c_addr customers.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);

EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/

No such customer!

PL/SQL procedure successfully completed.

The above program displays the name and address of a customer whose ID is given.
Since there is no customer with ID value 8 in our database, the program raises the run-
time exception NO_DATA_FOUND, which is captured in the EXCEPTION block.
create table stud(stud_id int , s_name varchar(20), marks int);
insert into stud values(1, 'Suraj',100);
3
insert into stud values(2, 'Praveen',97);
insert into stud values(3, 'Jessie', 99);

1. NO_DATA_FOUND: It is raised WHEN a SELECT INTO statement

returns no rows. For eg:
DECLARE
   temp varchar(20);
  
BEGIN
   SELECT stud_id into temp from stud where s_name='Geeta';
  
exception
   WHEN no_data_found THEN
      dbms_output.put_line('ERROR');
      dbms_output.put_line('there is no name as');
      dbms_output.put_line('Geeta in stud table');
Output:
ERROR
there is no name as Geeta in stud table
2. TOO_MANY_ROWS:It is raised WHEN a SELECT INTO statement
returns more than one row.
DECLARE
   temp varchar(20);
  
BEGIN
  
-- raises an exception as SELECT 
-- into trying to return too many rows
   SELECT s_name into temp from stud;
   dbms_output.put_line(temp);
  
EXCEPTION
   WHEN too_many_rows THEN
      dbms_output.put_line('error trying to SELECT too many
rows');
  
end;

4
 Output:
error trying to SELECT too many rows
3. VALUE_ERROR:This error is raised WHEN a statement is executed that
resulted in an arithmetic, numeric, string, conversion, or constraint error. This error
mainly results from programmer error or invalid data input.

DECLARE
   temp number;   
  
BEGIN
   SELECT s_name  into temp from stud where s_name='Suraj';
   dbms_output.put_line('the s_name is '||temp);
  
EXCEPTION
   WHEN value_error THEN
   dbms_output.put_line('Error');
   dbms_output.put_line('Change data type of temp to
varchar(20)');
  
END;
Output:
Error
Change data type of temp to varchar(20)
4. ZERO_DIVIDE = raises exception WHEN dividing with zero.
DECLARE
   a int:=10;
   b int:=0;
   answer int;
  
BEGIN
   answer:=a/b;
   dbms_output.put_line('the result after division is'||answer);
  
exception
   WHEN zero_divide THEN
      dbms_output.put_line('dividing by zero please check the
values again');

5
       dbms_output.put_line('the value of a is '||a);
      dbms_output.put_line('the value of b is '||b);
END;
Output:
dividing by zero please check the values again
the value of a is 10
the value of b is 0
Raising Exceptions
Exceptions are raised by the database server automatically whenever there is any internal
database error, but exceptions can be raised explicitly by the programmer by using the
command RAISE. Following is the simple syntax for raising an exception −
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
User-defined Exceptions
PL/SQL allows you to define your own exceptions according to the need of your
program. A user-defined exception must be declared and then raised explicitly, using
either a RAISE statement or the
procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR.
The syntax for declaring an exception is −
DECLARE
my-exception EXCEPTION;
Example
The following example illustrates the concept. This program asks for a customer ID,
when the user enters an invalid ID, the exception invalid_id is raised.
DECLARE
c_id customers.id%type := &cc_id;

6
 c_name customerS.Name%type;
c_addr customers.address%type;
-- user defined exception
ex_invalid_id EXCEPTION;
BEGIN
IF c_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
END IF;

EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line('ID must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
Enter value for cc_id: -6 (let's enter a value -6)
old 2: c_id customers.id%type := &cc_id;
new 2: c_id customers.id%type := -6;
ID must be greater than zero!
PL/SQL procedure successfully completed.
Pre-defined Exceptions
PL/SQL provides many pre-defined exceptions, which are executed when any database
rule is violated by a program. For example, the predefined exception
NO_DATA_FOUND is raised when a SELECT INTO statement returns no rows. The
following table lists few of the important pre-defined exceptions −

Oracle
Exception SQLCODE Description
Error

7
 It is raised when a null object is
ACCESS_INTO_NULL 06530 -6530
automatically assigned a value.

It is raised when none of the

choices in the WHEN clause of
CASE_NOT_FOUND 06592 -6592
a CASE statement is selected,
and there is no ELSE clause.

It is raised when a program

attempts to apply collection
methods other than EXISTS to
COLLECTION_IS_NUL an uninitialized nested table or
06531 -6531
L varray, or the program attempts
to assign values to the elements
of an uninitialized nested table
or varray.

It is raised when duplicate

DUP_VAL_ON_INDEX 00001 -1 values are attempted to be stored
in a column with unique index.

It is raised when attempts are

made to make a cursor operation
INVALID_CURSOR 01001 -1001
that is not allowed, such as
closing an unopened cursor.

It is raised when the conversion

of a character string into a
INVALID_NUMBER 01722 -1722 number fails because the string
does not represent a valid
number.

LOGIN_DENIED 01017 -1017 It is raised when a program

attempts to log on to the

8
 database with an invalid
username or password.

It is raised when a SELECT

NO_DATA_FOUND 01403 +100
INTO statement returns no rows.

It is raised when a database call

NOT_LOGGED_ON 01012 -1012 is issued without being
connected to the database.

It is raised when PL/SQL has an

PROGRAM_ERROR 06501 -6501
internal problem.

It is raised when a cursor fetches

ROWTYPE_MISMATCH 06504 -6504 value in a variable having
incompatible data type.

It is raised when a member

method is invoked, but the
SELF_IS_NULL 30625 -30625
instance of the object type was
not initialized.

It is raised when PL/SQL ran

STORAGE_ERROR 06500 -6500 out of memory or memory was
corrupted.

It is raised when a SELECT

TOO_MANY_ROWS 01422 -1422 INTO statement returns more
than one row.

It is raised when an arithmetic,

VALUE_ERROR 06502 -6502 conversion, truncation, or
sizeconstraint error occurs.

9
 It is raised when an attempt is
ZERO_DIVIDE 01476 1476 made to divide a number by
zero.

UTES

10
11