Relational Database Management System- 22CTU08
SRI KRISHNA ARTS AND SCIENCE COLLEGE
DEPARTMENT OF COMPUTER TECHNOLOGY AND
DATA SCIENCE
Academic Year 2023-24
Faculty Handling
G. Sumalatha
Assistant Professor
Department of computer Technology
SKASC 1
Relational Database Management System- 22CTU08
SRI KRISHNA ARTS AND SCIENCE COLLEGE
DEPARTMENT OF COMPUTER TECHNOLOGY AND
DATA SCIENCE
COURSE NAME : Relational Database Management System
COURSE CODE : 22CTU08
CLASS ROOM CODE : vcpp3ll
UNIT 5 – Handling Exceptions
SKASC 2
Relational Database Management System- 22CTU08
AGENDA
Recognize unhandled exceptions
List and useDefine PL/SQL exceptions
different types of PL/SQL exception handlers
Trap unanticipated errors
Describe the effect of exception propagation in nested blocks
Customize PL/SQL exception messages
SKASC 3
Relational Database Management System- 22CTU08
EXCEPTIONS
TOPIC LINK
Handling exception
https://youtu.be/P4U7Gzc2C3I
and types
Trapping of non
predefined oracle
https://youtu.be/itkqAotJp9c
server errors
SKASC 4
Relational Database Management System- 22CTU08
Objectives
After completing this lesson, you should be able to do the
following:
•Recognize unhandled exceptions
•List and useDefine PL/SQL exceptions
• different types of PL/SQL exception handlers
•Trap unanticipated errors
•Describe the effect of exception propagation in nested blocks
•Customize PL/SQL exception messages
SKASC 5
Relational Database Management System- 22CTU08
Example of an
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;
SKASC 6
Relational Database Management System- 22CTU08
Example of an 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);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE (' Your select statement
retrieved multiple rows. Consider using a
cursor.');
END;
/
SKASC 7
Relational Database Management System- 22CTU08
Handling Exceptions with PL/SQL
◼ An exception is a PL/SQL error that is raised during
program execution.
◼ An exception can be raised:
Implicitly by the Oracle server
Explicitly by the program
◼ An exception can be handled:
By trapping it with a handler
By propagating it to the calling environment
SKASC 8
Relational Database Management System- 22CTU08
Handling Exceptions
Is the
exception Terminat
trapped? N e
o abruptly.
Ye
s
Exceptio Execute Propagate
n statements the
is raised. in the EXCEPTION
exception.
section.
Terminate
gracefully.
SKASC 9
Relational Database Management System- 22CTU08
Exception Types
– Predefined Oracle server
– Non-predefined Oracle server
– User-defined
SKASC 10
Relational Database Management System- 22CTU08
Trapping Exceptions
Syntax:
EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
statement1;
statement2;
. . .
[WHEN exception3 [OR exception4 . . .] THEN
statement1;
statement2;
. . .]
[WHEN OTHERS THEN
statement1;
statement2;
. . .]
SKASC 11
Relational Database Management System- 22CTU08
WHEN OTHERS Exception Handler
The exception-handling section traps only those exceptions that
are specified; any other exceptions are not trapped unless you use
the OTHERS exception handler.
This traps any exception not yet handled.
For this reason, OTHERS may be used, and if used it must be the
last exception handler that is defined.
WHEN NO_DATA_FOUND THEN
statement1;
...
WHEN TOO_MANY_ROWS THEN
statement1;
...
WHEN OTHERS THEN
statement1;
SKASC 12
Relational Database Management System- 22CTU08
Guidelines for Trapping
Exceptions
◼ The EXCEPTION keyword starts the
exception-handling section.
◼ Several exception handlers are allowed.
◼ Only one handler is processed before leaving the
block.
◼ WHEN OTHERS is the last clause.
SKASC 13
Relational Database Management System- 22CTU08
Trapping Predefined Oracle
Server Errors
◼ Reference the predefined name in the exception-handling
routine.
◼ Sample predefined exceptions:
NO_DATA_FOUND
TOO_MANY_ROWS
INVALID_CURSOR
ZERO_DIVIDE
DUP_VAL_ON_INDEX
SKASC 14
Relational Database Management System- 22CTU08
Trapping Non-Predefined
Oracle Server Errors
Non-predefined exceptions are similar to predefined exceptions;
however, they are not defined as PL/SQL exceptions in the Oracle server.
They are standard Oracle errors. You create exceptions with standard
Oracle errors by using the PRAGMA EXCEPTION_INIT function.
Such exceptions are called non-predefined exceptions.
You can trap a non-predefined Oracle server error by declaring it first.
The declared exception is raised implicitly.
In PL/SQL, PRAGMA EXCEPTION_INIT tells the compiler to
associate an exception name with an Oracle error number.
That enables you to refer to any internal exception by name and to write
a specific handler for it.
SKASC 15
Relational Database Management System- 22CTU08
Trapping Non-Predefined
Oracle Server Errors
Declar Associat Referenc
e e e
Declarative EXCEPTION
section section
Name the Use PRAGMA Handle the raised
exception. EXCEPTION_INIT. exception.
SKASC 16
Relational Database Management System- 22CTU08
Non-Predefined Error
1. Declare the name of the exception in the declarative section.
Syntax:
exception EXCEPTION;
In the syntax, exception is the name of the exception.
2.Associate the declared exception with the standard Oracle server
error number using the PRAGMA EXCEPTION_INIT function.
Syntax:
PRAGMA EXCEPTION_INIT(exception, error_number);
In the syntax, exception is the previously declared exception and
error_number is a standard Oracle server error number.
3. Reference the declared exception within the corresponding
exception-handling routine.
SKASC 17
Relational Database Management System- 22CTU08
SUMMARY
Recognize unhandled exceptions
List and useDefine PL/SQL exceptions
different types of PL/SQL exception handlers
Trap unanticipated errors
Describe the effect of exception propagation in nested blocks
Customize PL/SQL exception messages
SKASC 18
Relational Database Management System- 22CTU08
KEYWORDS
Exceptions
Usedefine PL/SQL Exceptions
Exception Handlers
Unanticipated Errors
Exception Propagation In Nested Blocks
Exception Messages
SKASC 19
Relational Database Management System- 22CTU08
MCQ
1. How many types of exception are there in sql? *
a) 2
b) 3
c) 4
c) 5
SKASC 20
Relational Database Management System- 22CTU08
MCQ
2. Name the exception which is raised when a null object is automatically
assigned a value?
a) CASE_NOT_FOUND
b) COLLECTION_IS_NULL
c) ACCESS_INTO_NULLd
d) DUP_VAL_ON_INDEX
SKASC 21
Relational Database Management System- 22CTU08
MCQ
3.What is oracle error code for exception COLLECTION_IS_NULL? *
a) "06530"
b) "06592"
c) "06531"
d) "01722"
SKASC 22
Relational Database Management System- 22CTU08
MCQ
4. An exception is an error condition during a program execution.
*
a) TRUE
b) FALSE
c) CAN BE TRUE OR FALSE
d) CANNOT SAY
SKASC 23
Relational Database Management System- 22CTU08
MCQ
5.The default exception will be handled using? *
a) Default Keyword
b) WHEN THEN Keyword
c) WHEN Keyword
d) WHEN others THEN Keyword
SKASC 24
Relational Database Management System- 22CTU08
NEXT SESSION
Handling Exceptions
SKASC 25
