Do you think IIT Guwahati certified course can help you in your career?
No
Introduction
An exception in PL/SQL is an error that occurs during the program's execution.
The exception block in PL/SQL allows programmers to capture such occurrences, taking appropriate action against the error condition.
There are two types of exceptions:
System-defined Exceptions
User-defined Exceptions
Raising Exceptions
Exceptions are raised automatically by the database server in the event of any internal database fault. However, the programmer can explicitly raise it by using the RAISE command.
The following is the syntax for raising an exception:
WHEN exception THEN
statement;
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 users to define their exceptions based on the program's requirements. A RAISE statement or the function DBMS_STANDARD.RAISE_APPLICATION_ERROR can be used to raise a user-defined exception explicitly.
User-defined exceptions have a specific syntax.
DECLARE
my-exception EXCEPTION;
Example of exception handling
To demonstrate the concept of exception handling, let's look at a basic example. We will use the STUDENTS table, which has already been created.
SELECT* FROM STUDENTS;
ID
NAME
AGE
ADDRESS
MARKS
1
Dinesh
22
Ahmedabad
80
2
Ramesh
19
Pune
74
3
Mukesh
22
Goa
45
4
Suresh
21
Chennai
34
5
Amisha
20
Delhi
78
6
Anushka
20
Delhi
88
DECLARE
s_id students.id%type;
s_name students.name%type;
s_addr students.address%type:= ‘Mumbai’;
BEGIN
SELECT name, address INTO s_name, s_addr
FROM students
WHERE address = s_addr;
DBMS_OUTPUT.PUT_LINE ('Name: '|| s_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || s_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such student!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
You should obtain the following result after running the above code at the SQL prompt:
No such student!
PL/SQL procedure completed.
The above programs should display the name and address whose address has been provided. The software throws the run-time exception NO DATA FOUND, which is captured in the EXCEPTION block because no student has the address value ‘Mumbai’ in our database.
We will obtain a specific result if we utilize the address defined in the above table. For example, we'll use the address ‘Ahmedabad’.
DECLARE
s_id students.id%type;
s_name students.name%type;
s_addr students.address%type := ‘Ahmedabad’;
BEGIN
SELECT name, address INTO s_name, s_addr
FROM students
WHERE address = s_addr;
DBMS_OUTPUT.PUT_LINE ('Name: '|| s_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || s_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such student!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
/
You should obtain the following result after running the above code at the SQL prompt:
Several pre-defined exceptions are triggered when the applications break a database rule.
For example, when a SELECT INTO command produces no rows, a pre-defined exception called NO_DATA_FOUND is thrown.
Here's a summary of several pre-defined exceptions:
Exception
Oracle Error
SQL Code
Description
ACCESS_INTO_NULL
06530
-6530
This exception is triggered when a NULL object is automatically allocated a value.
CASE_NOT_FOUND
06592
-6592
It is triggered when none of the options in a CASE statement's "WHEN" clauses are picked, and there is no else clause.
COLLECTION_IS_NULL
06531
-6531
It is raised when a program tries to apply collection methods to an uninitialized nested table or varray that does not exist.
DUP_VAL_ON_INDEX
00001
-1
This error is raised when duplicate values are stored in a column with a unique index.
INVALID_CURSOR
01001
-1001
It is raised when an unallowable cursor operation is attempted, such as closing an unopened cursor.
INVALID_NUMBER
01722
-1722
When converting a character string to a number fails due to the string not representing a valid number, this exception is raised.
LOGIN_DENIED
01017
-1017
It's triggered when a program tries to access the database with an incorrect username or password.
NO_DATA_FOUND
01403
+100
When a select into command returns no rows, this error is raised.
NOT_LOGGED_ON
01012
-1012
This exception is generated when a database call is made without connecting to the database.
PROGRAM_ERROR
06501
-6501
When PL/SQL has an internal fault, this exception is triggered.
ROWTYPE_MISMATCH
06504
-6504
This exception is raised when a cursor retrieves a value from a variable with an incompatible data type.
SELF_IS_NULL
30625
-30625
This error is thrown when a member method is called, but the object type instance isn't initialized.
STORAGE_ERROR
06500
-6500
This exception is thrown when PL/SQL runs out of memory or is damaged.
TOO_MANY_ROWS
01422
-1422
This exception is thrown when a SELECT INTO command returns more than one row.
VALUE_ERROR
06502
-6502
This exception is raised when the arithmetic, conversion, truncation, or size-constraint error occurs.
ZERO_DIVIDE
01476
1476
When attempting to divide an integer by zero, this exception is raised.
FAQs
What is exception handling in PL/SQL? An exception is an error that causes the normal flow of instructions. The exception block in PL/SQL raises the exception, assisting the programmer in locating and resolving the problem.
What are the different exceptions defined in PL/SQL? In PL/SQL, there are two types of exceptions: User-defined exception. System-defined exceptions.
What is the general syntax to write an exception? The following is the syntax for raising an exception:
WHEN exception THEN
statement;
Key Takeaways
In this blog, we have learned that an Exception is an error that happens during the execution of a program. We have seen types of exception handling and learned how to raise an exception and its syntax with examples of how to use them.
Also, try Coding Ninjas Studio to practice programming problems for your complete interview preparation. Ninja, don't stop here; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.