In this blog, we’ll discuss Exceptions in PL/SQL. An exception is a mistake condition during a program execution. PL/SQL supports programmers to catch such conditions using EXCEPTION block within the program and an appropriate action is taken against the error condition. There are two sorts of exceptions −
• System-defined exceptions
• User-defined exceptions
Syntax for Exception Handling
The general syntax for exception handling is as follows. Here you’ll list down as many exceptions as you’ll handle. The default exception are going to be handled using WHEN others THEN −

DECLARE 

BEGIN 

EXCEPTION 

WHEN exception1 THEN 
exception1-handling-statements 
WHEN exception2 THEN 
exception2-handling-statements 
WHEN exception3 THEN 
exception3-handling-statements 
........ 
WHEN others THEN 
exception3-handling-statements 
END;

Example
Let us write a code for instance the concept. we’ll be using the purchasers table we had created and utilized in the previous chapters −

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; 
/

When the above code is executed at the SQL prompt, it produces the subsequent 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’s no customer with ID value 8 in our database, the program raises the run-time exception NO_DATA_FOUND, which is captured within the EXCEPTION block.
Raising Exceptions
Exceptions are raised by the database server automatically whenever there’s any internal database error, but exceptions are often raised explicitly by the programmer by using the command RAISE. Following is that 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. within the next section, we’ll offer you an example on raising a user-defined exception. you’ll raise the Oracle standard exceptions during a similar way.
User-defined Exceptions
PL/SQL allows you to define your own exceptions consistent with the necessity of your program. A user-defined exception must be declared 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; 
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 subsequent result −
Enter value for cc_id: -6 (let's enter a worth -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 instance, the predefined exception NO_DATA_FOUND is raised when a get INTO statement returns no rows. the subsequent table lists few of the important pre-defined exceptions –

Exception	Oracle Error	SQLCODE	Description
ACCESS_INTO_NULL	06530	-6530	It is raised when a null object is automatically assigned a worth.
CASE_NOT_FOUND	06592	-6592	It is raised when none of the choices in the WHEN clause of a CASE statement is selected, and there’s no ELSE clause.
COLLECTION_IS_NULL	06531	-6531	It is raised when a program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
DUP_VAL_ON_INDEX	00001	-1	It is raised when duplicate values are attempted to be stored during a column with unique index.
INVALID_CURSOR	01001	-1001	It is raised when attempts are made to form a cursor operation that’s not allowed, like closing an unopened cursor.
INVALID_NUMBER	01722	-1722	It is raised when the conversion of a personality string into variety fails because the string doesn’t represent a legitimate number.
LOGIN_DENIED	01017	-1017	It is raised when a program attempts to go online to the database with an invalid username or password.
NO_DATA_FOUND	01403	+100	It is raised when a get INTO statement returns no rows.
NOT_LOGGED_ON	01012	-1012	It is raised when a database call is issued without being connected to the database.
PROGRAM_ERROR	06501	-6501	It is raised when PL/SQL has an indoor problem.
ROWTYPE_MISMATCH	06504	-6504	It is raised when a cursor fetches value during a variable having incompatible data type.
SELF_IS_NULL	30625	-30625	It is raised when a member method is invoked, but the instance of the thing type wasn’t initialized.
STORAGE_ERROR	06500	-6500	It is raised when PL/SQL ran out of memory or memory was corrupted.
TOO_MANY_ROWS	01422	-1422	It is raised when a SELECT INTO statement returns quite one row.
VALUE_ERROR	06502	-6502	It is raised when an arithmetic, conversion, truncation, or size constraint error occurs.
ZERO_DIVIDE	01476	1476	It is raised when an effort is formed to divide a variety by zero.

So, this brings us to the end of blog. This Tecklearn ‘Exceptions Handling in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

614