EXP15

EXPERIMENT 15
EXCEPTIONS
AIM
To familiarize exceptions in DBMS
DESCRIPTION
An Exception is an error situation, which arises during program execution. When an error
occurs exception is raised, normal execution is stopped and control transfers to exception
handling part.
Exception handlers are routines written to handle the exception. The exceptions can be
internally defined (system-defined or pre-defined) or User-defined exception.
Syntax:
EXCEPTION
WHEN <ExceptionName> THEN
<User Defined Action To Be Carried Out>
Predefined exception:
It is raised automatically whenever there is a violation of Oracle coding rules. Predefined
exceptions are those like ZERO_DIVIDE, which is raised automatically when we try to
divide a number by zero. Other built-in exceptions are given below. You can handle
unexpected Oracle errors using OTHERS handler. It can handle all raised exceptions that are
not handled by any other handler. It must always be written as the last handler in exception
block.
Exception Raised when....
DUP_VAL_ON_INDEX When you try to insert a duplicate value into a unique column.
INVALID_CURSOR It occurs when we try accessing an invalid cursor.
INVALID_NUMBER On usage of something other than number in place of number value.
LOGIN_DENIED At the time when user login is denied.
TOO_MANY_ROWS When a select query returns more than one row and the destination
variable can take only single value.
VALUE_ERROR When an arithmetic, value conversion, truncation, or constraint error

occurs.
CURSOR_ALREADY_OPEN Raised when we try to open an already open cursor.

Predefined exception handlers are declared globally in package STANDARD. Hence we need
not have to define them rather just use them. The biggest advantage of exception handling is
it improves readability and reliability of the code. Errors from many statements of code can
be handles with a single handler. Instead of checking for an error at every point we can just
add an exception handler and if any exception is raised it is handled by that.
For checking errors at a specific spot it is always better to have those statements in a separate
begin – end block.
User-defined Exceptions :
The technique that is used is to bind a numbered exception handler to a name using Pragma
Exception_init (). This binding of a numbered exception handler, to a name (i.e. a String), is
done in the Declare section of a PL/SQL block.
The Pragma action word is a call to a pre-compiler, which immediately binds the numbered
exception handler to a name when encountered.
The function Exception_init() takes two parameters the first is the user defined exception
name the second is the Oracle engine's exception number. These lines will be included in the
Declare section of the PL/SQL block.
The user defined exception name must be the statement that immediately precedes the
Pragma Exception_init() statement.
Syntax:
DECLARE
< ExceptionName > EXCEPTION ;
PRAGMA EXCEPTION_INIT (< ExceptionName >, <ErrorCodeNo>);
BEGIN
Using this technique it is possible to bind appropriate numbered exception handlers to names
and use these names in the Exception section of a PL/SQL block. When this is done the
default exception handling code of the exception handler is overridden and the user-defined
exception handling code is executed .
Syntax:
DECLARE
PRAGMA EXCEPTION_INIT (< ExceptionName >, <ErrorCodeNo>);
BEGIN
. . . .
EXCEPTION
WHEN < ExceptionName > THEN
< Action >
END;
User Defined Exception Handling :

To trap business rules being violated the technique of raising user-defined exceptions and
then handling them, is used.
User-defined error conditions must be declared in the declarative part of any PL/SQL block.
In the executable part, a check for the condition that needs special attention is made. If that
condition exists, the call to the user-defined exception is made using a RAISE statement. The
exception once raised is then handled in the Exception handling section of the PL/SQL code
block.
Syntax:
DECLARE
BEGIN
<SQL Sentence >;
IF < Condition > THEN
RAISE <ExceptionName>;
END IF;
EXCEPTION
WHEN <ExceptionName>THEN {User Defined Action To Be Taken};
END;
Questions
1. Write a PL/SQL block to raise an exception when a division by zero occurs.
2. Create table named emp have column like id with notnull constraint,name etc. Raise
an exception “Not null values not allowed “ when the user tries to enter a null value
for id.
3. Write a PL/SQL block to raise an exception “No transactions today”,when the sysday
is Sunday.
Answers
1.
DECLARE
N number;
BEGIN
N:=10/0;
EXCEPTION WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('divide by zero error occures..');
END;
/
2.
DECLARE
e_MissingNull EXCEPTION;
PRAGMA EXCEPTION_INIT(e_MissingNull, -1400);
BEGIN
INSERT INTO emp(id) VALUES (NULL);
EXCEPTION
WHEN e_MissingNull then
DBMS_OUTPUT.put_line('ORA-1400 occurred');
END;
/
3.
DECLARE
ex EXCEPTION;
BEGIN
IF TO_CHAR(SYSDATE,'DY')=='SUN' THEN
RAISE ex;
END IF;
EXCEPTION
WHEN ex then
DBMS_OUTPUT.put_line('No Transcations Today');
END;
/

EXP15

Uploaded by

Copyright:

Available Formats

EXP15

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

EXP15

Uploaded by

Copyright:

Available Formats

EXPERIMENT 15

Exception Raised when....

INVALID_CURSOR It occurs when we try accessing an invalid cursor.

INVALID_NUMBER On usage of something other than number in place of number value.

LOGIN_DENIED At the time when user login is denied.

VALUE_ERROR When an arithmetic, value conversion, truncation, or constraint error

CURSOR_ALREADY_OPEN Raised when we try to open an already open cursor.

User Defined Exception Handling :

You might also like