Page 1 of 8

Home Whiteboard Online Compilers Practice Articles AI Assistant

SQL HTML CSS Javascript Python Java C C++ PHP Scala C#

PL/SQL - Exceptions

In this chapter, we will discuss Exceptions in PL/SQL. 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 for Exception Handling

The general syntax for exception handling is as follows. Here you can list down as many
exceptions as you can handle. The default exception will be handled using WHEN others
THEN −

DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling goes here >
WHEN exception1 THEN
exception1-handling-statements
WHEN exception2 THEN
exception2-handling-statements
WHEN exception3 THEN
exception3-handling-statements
........
WHEN others THEN
 Page 2 of 8

exception3-handling-statements
END;

Example

Let us write a code to illustrate the concept. We will be using the CUSTOMERS table we
had created and used in the previous chapters −

DECLARE
c_id [Link]%type := 8;
c_name [Link]%type;
c_addr [Link]%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;
/

When the above code is executed at the SQL prompt, it produces the following result −

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.

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
 Page 3 of 8

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;

You can use the above syntax in raising the Oracle standard exception or any user-
defined exception. In the next section, we will give you an example on raising a user-
defined exception. You can raise the Oracle standard exceptions in a similar way.

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 [Link]%type := &cc_id;
c_name [Link]%type;
c_addr [Link]%type;
-- user defined exception
ex_invalid_id EXCEPTION;
BEGIN
IF c_id <= 0 THEN
 Page 4 of 8

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 [Link]%type := &cc_id;
new 2: c_id [Link]%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

It is raised when a null object is

ACCESS_INTO_NULL 06530 -6530
automatically assigned a value.
 Page 5 of 8

Chapters It is raised when none Categories

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

It is raised when a program attempts

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

It is raised when duplicate values are

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

It is raised when attempts are made to

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

It is raised when the conversion of a

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

It is raised when a program attempts

LOGIN_DENIED 01017 -1017 to log on to the database with an
invalid username or password.

It is raised when a SELECT INTO

NO_DATA_FOUND 01403 +100
statement returns no rows.

It is raised when a database call is

NOT_LOGGED_ON 01012 -1012 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 value

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

It is raised when a member method is

SELF_IS_NULL 30625 -30625 invoked, but the instance of the object
type was not initialized.
 Page 6 of 8

It is raised when PL/SQL ran out of

STORAGE_ERROR 06500 -6500
memory or memory was corrupted.

It is raised when a SELECT INTO

TOO_MANY_ROWS 01422 -1422
statement returns more than one row.

It is raised when an arithmetic,

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

It is raised when an attempt is made

ZERO_DIVIDE 01476 1476
to divide a number by zero.

TOP TUTORIALS

Python Tutorial
Java Tutorial
C++ Tutorial

C Programming Tutorial
C# Tutorial
PHP Tutorial

R Tutorial
HTML Tutorial
CSS Tutorial

JavaScript Tutorial
SQL Tutorial

TRENDING TECHNOLOGIES

Cloud Computing Tutorial

Amazon Web Services Tutorial

Microsoft Azure Tutorial

Git Tutorial
Ethical Hacking Tutorial

Docker Tutorial
Kubernetes Tutorial
DSA Tutorial

Spring Boot Tutorial

 Page 7 of 8

SDLC Tutorial
Unix Tutorial

CERTIFICATIONS

Business Analytics Certification

Java & Spring Boot Advanced Certification

Data Science Advanced Certification

Cloud Computing And DevOps
Advanced Certification In Business Analytics

Artificial Intelligence And Machine Learning

DevOps Certification
Game Development Certification

Front-End Developer Certification

AWS Certification Training
Python Programming Certification

COMPILERS & EDITORS

Online Java Compiler

Online Python Compiler

Online Go Compiler
Online C Compiler

Online C++ Compiler

Online C# Compiler
Online PHP Compiler

Online MATLAB Compiler

Online Bash Terminal
Online SQL Compiler

Online Html Editor

ABOUT US | OUR TEAM | CAREERS | JOBS | CONTACT US | TERMS OF USE |

PRIVACY POLICY | REFUND POLICY | COOKIES POLICY | FAQ'S

 Page 8 of 8

Tutorials Point is a leading Ed Tech company striving to provide the best learning material on
technical and non-technical subjects.

© Copyright 2025. All Rights Reserved.