UN UNIT-3
PL/SQL - Exceptions
. 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
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 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 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 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_idis 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 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 −
Exception
Oracle
Error
SQLCODE
Description
ACCESS_INTO_NULL
06530
-6530
It is raised when
a null object is
automatically
assigned a value.
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 is 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 in a
column with
unique index.
INVALID_CURSOR
01001
-1001
It is raised when
attempts are
made to make a
cursor operation
that is not
allowed, such as
closing an
unopened
cursor.
INVALID_NUMBER
01722
-1722
It is raised when
the conversion
of a character
string into a
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
database with an
invalid username
or password.
NO_DATA_FOUND
01403
+100
It is raised when
a SELECT 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
internal
problem.
ROWTYPE_MISMATCH
06504
-6504
It is raised when
a cursor fetches
value in 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
object type was
not 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 more
than one row.
VALUE_ERROR
06502
-6502
It is raised when
an arithmetic,
conversion,
truncation, or
sizeconstraint
error occurs.
ZERO_DIVIDE
01476
1476
It is raised when
an attempt is
made to divide a
number by zero
CURSORS
The Oracle engine uses a work area(context area) for its internal processing in order to execute
an SQL statement.This work area is private to SQL’s operations and is called cursor.
The data that is stored in the cursor is called Active Data Set.
There are two types of cursors –depending upon the circumstances under which they are
opened.
Implicit cursors
Explicit cursors
Implicit Cursors
Implicit cursors are automatically created by Oracle whenever an SQL statement is executed,
when there is no explicit cursor for the statement. Programmers cannot control the implicit
cursors and the information in it.
Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is
associated with this statement. For INSERT operations, the cursor holds the data that needs to
be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be
affected.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always
has attributes such as %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT. The SQL
cursor has additional attributes, %BULK_ROWCOUNT and %BULK_EXCEPTIONS, designed
for use with the FORALL statement. The following table provides the description of the most
used attributes −
S.No
Attribute & Description
1
%FOUND
Returns TRUE if an INSERT, UPDATE,
or DELETE statement affected one or
more rows or a SELECT INTO
statement returned one or more rows.
Otherwise, it returns FALSE.
2
%NOTFOUND
The logical opposite of %FOUND. It
returns TRUE if an INSERT, UPDATE,
or DELETE statement affected no rows,
or a SELECT INTO statement returned
no rows. Otherwise, it returns FALSE.
3
%ISOPEN
Always returns FALSE for implicit
cursors, because Oracle closes the SQL
cursor automatically after executing its
associated SQL statement.
4
%ROWCOUNT
Returns the number of rows affected by
an INSERT, UPDATE, or DELETE
statement, or returned by a SELECT
INTO statement.
Any SQL cursor attribute will be accessed as sql%attribute_name as shown below in the
example.
Example
We will be using the CUSTOMERS table we had created and used in the previous chapters.
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+----+----------+-----+-----------+----------+
The following program will update the table and increase the salary of each customer by 500
and use the SQL%ROWCOUNT attribute to determine the number of rows affected −
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
6 customers selected
PL/SQL procedure successfully completed.
If you check the records in customers table, you will find that the rows have been updated −
Select * from customers;
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2500.00 |
| 2 | Khilan | 25 | Delhi | 2000.00 |
| 3 | kaushik | 23 | Kota | 2500.00 |
| 4 | Chaitali | 25 | Mumbai | 7000.00 |
| 5 | Hardik | 27 | Bhopal | 9000.00 |
| 6 | Komal | 22 | MP | 5000.00 |
+----+----------+-----+-----------+----------+
Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control over the context area.
An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created
on a SELECT Statement which returns more than one row.
The syntax for creating an explicit cursor is −
CURSOR cursor_name IS select_statement;
Working with an explicit cursor includes the following steps −
Declaring the cursor for initializing the memory
Opening the cursor for allocating the memory
Fetching the cursor for retrieving the data
Closing the cursor to release the allocated memory
Declaring the Cursor
Declaring the cursor defines the cursor with a name and the associated SELECT statement. For
example −
CURSOR c_customers IS
SELECT id, name, address FROM customers;
Opening the Cursor
Opening the cursor allocates the memory for the cursor and makes it ready for fetching the rows
returned by the SQL statement into it. For example, we will open the above defined cursor as
follows −
OPEN c_customers;
Fetching the Cursor
Fetching the cursor involves accessing one row at a time. For example, we will fetch rows from
the above-opened cursor as follows −
FETCH c_customers INTO c_id, c_name, c_addr;
Closing the Cursor
Closing the cursor means releasing the allocated memory. For example, we will close the
above-opened cursor as follows −
CLOSE c_customers;
Example
Following is a complete example to illustrate the concepts of explicit cursors &minua;
DECLARE
c_id customers.id%type;
c_name customerS.No.ame%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP
PL/SQL procedure successfully completed.
PROCEDURES
PL/SQL subprograms are named PL/SQL blocks that can be invoked with a set of parameters.
PL/SQL provides two kinds of subprograms −
Functions − These subprograms return a single value; mainly used to compute and
return a value.
Procedures − These subprograms do not return a value directly; mainly used to perform
an action.
This chapter is going to cover important aspects of a PL/SQL procedure. We will
discuss PL/SQL function in the next chapter.
Parts of a PL/SQL Subprogram
Each PL/SQL subprogram has a name, and may also have a parameter list. Like anonymous
PL/SQL blocks, the named blocks will also have the following three parts −
S.No
Parts & Description
1
Declarative Part
It is an optional part. However, the
declarative part for a subprogram does
not start with the DECLARE keyword. It
contains declarations of types, cursors,
constants, variables, exceptions, and
nested subprograms. These items are
local to the subprogram and cease to
exist when the subprogram completes
execution.
2
Executable Part
This is a mandatory part and contains
statements that perform the designated
action.
3
Exception-handling
This is again an optional part. It contains
the code that handles run-time errors.
Creating a Procedure:
A procedure is created with the CREATE OR REPLACE PROCEDURE statement. The
simplified syntax for the CREATE OR REPLACE PROCEDURE statement is as follows −
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
{IS | AS}
BEGIN
< procedure_body >
END procedure_name;
Where,
procedure-name specifies the name of the procedure.
[OR REPLACE] option allows the modification of an existing procedure.
The optional parameter list contains name, mode and types of the
parameters. IN represents the value that will be passed from outside and OUT
represents the parameter that will be used to return a value outside of the procedure.
procedure-body contains the executable part.
The AS keyword is used instead of the IS keyword for creating a standalone procedure.
Example
The following example creates a simple procedure that displays the string 'Hello World!' on the
screen when executed.
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
dbms_output.put_line('Hello World!');
END;
/
When the above code is executed using the SQL prompt, it will produce the following result −
Procedure created.
Executing a Standalone Procedure
A standalone procedure can be called in two ways −
Using the EXECUTE keyword
Calling the name of the procedure from a PL/SQL block
The above procedure named 'greetings' can be called with the EXECUTE keyword as −
EXECUTE greetings;
The above call will display −
Hello World
PL/SQL procedure successfully completed.
The procedure can also be called from another PL/SQL block −
BEGIN
greetings;
END;
/
The above call will display −
Hello World
PL/SQL procedure successfully completed.
Deleting a Standalone Procedure
A standalone procedure is deleted with the DROP PROCEDURE statement. Syntax for deleting
a procedure is −
DROP PROCEDURE procedure-name;
You can drop the greetings procedure by using the following statement −
DROP PROCEDURE greetings;
Parameter Modes in PL/SQL Subprograms
The following table lists out the parameter modes in PL/SQL subprograms −
S.No
Parameter Mode & Description
1
IN
An IN parameter lets you pass a value to
the subprogram. It is a read-only
parameter. Inside the subprogram, an
IN parameter acts like a constant. It
cannot be assigned a value. You can pass
a constant, literal, initialized variable, or
expression as an IN parameter. You can
also initialize it to a default value;
however, in that case, it is omitted from
the subprogram call. It is the default
mode of parameter passing.
Parameters are passed by reference.
2
OUT
An OUT parameter returns a value to the
calling program. Inside the subprogram,
an OUT parameter acts like a variable.
You can change its value and reference
the value after assigning it. The actual
parameter must be variable and it is
passed by value.
3
IN OUT
An IN OUT parameter passes an initial
value to a subprogram and returns an
updated value to the caller. It can be
assigned a value and the value can be
read.
The actual parameter corresponding to
an IN OUT formal parameter must be a
variable, not a constant or an expression.
Formal parameter must be assigned a
value. Actual parameter is passed by
value.
IN & OUT Mode Example 1
This program finds the minimum of two values. Here, the procedure takes two numbers using
the IN mode and returns their minimum using the OUT parameters.
DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
Minimum of (23, 45) : 23
PL/SQL procedure successfully completed.
IN & OUT Mode Example 2
This procedure computes the square of value of a passed value. This example shows how we
can use the same parameter to accept a value and then return another result.
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
END;
BEGIN
a:= 23;
squareNum(a);
dbms_output.put_line(' Square of (23): ' || a);
END;
/
When the above code is executed at the SQL prompt, it produces the following result −
Square of (23): 529
PL/SQL procedure successfully completed.
Methods for Passing Parameters
Actual parameters can be passed in three ways −
Positional notation
Named notation
Mixed notation
Positional Notation
In positional notation, you can call the procedure as −
findMin(a, b, c, d);
In positional notation, the first actual parameter is substituted for the first formal parameter; the
second actual parameter is substituted for the second formal parameter, and so on. So, a is
substituted for x, b is substituted for y, c is substituted for z and d is substituted for m.
Named Notation
In named notation, the actual parameter is associated with the formal parameter using
the arrow symbol ( => ). The procedure call will be like the following −
findMin(x => a, y => b, z => c, m => d);
Mixed Notation
In mixed notation, you can mix both notations in procedure call; however, the positional notation
should precede the named notation.
The following call is legal −
findMin(a, b, c, m => d);
However, this is not legal:
findMin(x => a, b, c, d);
.
