Exceptions:
Error
occurring at run time is to handle the error is called exception.
Warring
or error code is called an exception.
Exceptions
can be internally defined (by the run-time system) or user defined
1. internally
defined exceptions
2. user
defined exceptions
Internally Defined Exceptions:
common
internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR.
The other internal exceptions can be given names.
Predefined PL/SQL
Exceptions:
An internal exception is raised implicitly whenever
your PL/SQL program violates an Oracle rule.
|
Exception
|
|
|
|
Your
program attempts to assign values to the attributes of an uninitialized
(atomically null) object.
|
|
|
None of
the choices in the WHEN clauses
of a CASE statement
is selected, and there is no ELSE clause.
|
|
|
Your
program attempts to apply collection methods other than EXISTS to an
uninitialized (atomically null) nested table or varray, or the program
attempts to assign values to the elements of an uninitialized nested table or
varray.
|
|
|
Your
program attempts to open an already open cursor. A cursor must be closed
before it can be reopened. A cursor FOR loop automatically opens the cursor
to which it refers. So, your program cannot open that cursor inside the loop.
|
|
|
Your
program attempts to store duplicate values in a database column that is
constrained by a unique index.
|
|
|
Your
program attempts an illegal cursor operation such as closing an unopened
cursor.
|
|
|
In a SQL
statement, the conversion of a character string into a number fails because
the string does not represent a valid number. (In procedural statements,
VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate
to a positive number.
|
|
|
Your
program attempts to log on to Oracle with an invalid username and/or
password.
|
|
|
A SELECT
INTO statement returns no rows, or your program references a deleted element
in a nested table or an uninitialized element in an index-by table. SQL
aggregate functions such as AVG and SUM always return a value or a null. So,
a SELECT INTO statement that calls an aggregate function never raises
NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually,
so when that happens, no exception is raised.
|
|
|
Your
program issues a database call without being connected to Oracle.
|
|
|
PL/SQL
has an internal problem.
|
|
|
The host
cursor variable and PL/SQL cursor variable involved in an assignment have
incompatible return types. For example, when an open host cursor variable is
passed to a stored subprogram, the return types of the actual and formal
parameters must be compatible.
|
|
|
Your
program attempts to call a MEMBER method on a null instance. That is, the
built-in parameter SELF (which is always the first parameter passed to a
MEMBER method) is null.
|
|
|
PL/SQL
runs out of memory or memory has been corrupted.
|
|
|
Your
program references a nested table or varray element using an index number
larger than the number of elements in the collection.
|
|
|
Your
program references a nested table or varray element using an index number (-1
for example) that is outside the legal range.
|
|
|
The
conversion of a character string into a universal rowid fails because the
character string does not represent a valid rowid.
|
|
|
A
time-out occurs while Oracle is waiting for a resource.
|
|
|
A SELECT
INTO statement returns more than one row.
|
|
|
An
arithmetic, conversion, truncation, or size-constraint error occurs. For
example, when your program selects a column value into a character variable,
if the value is longer than the declared length of the variable, PL/SQL
aborts the assignment and raises VALUE_ERROR. In procedural statements,
VALUE_ERROR is raised if the conversion of a character string into a number
fails. (In SQL statements, INVALID_NUMBER is raised.)
|
|
|
Your
program attempts to divide a number by zero.
|
Exceptions can be
declared only in the declarative part of a PL/SQL block,
subprogram,
or package.
RAISE_APPLICATION_ERROR:
To
call RAISE_APPLICATION_ERROR, use the syntax
raise_application_error(error_number,
message[, {TRUE | FALSE}]);
1.
where
error_number is a negative integer in the range -20000 .. -20999
2.
message
is a character string up to 2048 bytes long.
3.
optional
third parameter is TRUE,
CREATE
PROCEDURE raise_salary (emp_id NUMBER, amount NUMBER) AS
SELECT
sal INTO curr_sal FROM emp WHERE empno = emp_id;
/*
Issue user-defined error message. */
raise_application_error(-20101, 'Salary
is missing');
UPDATE
emp SET sal = curr_sal + amount WHERE empno = emp_id;
PRAGMA EXCEPTION
Associating
a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT
PRAGMA
EXCEPTION_INIT(exception_name, -Oracle_error_number);
deadlock_detected
EXCEPTION;
PRAGMA
EXCEPTION_INIT(deadlock_detected, -60);
DECLARE
deadlock_detected
EXCEPTION;
PRAGMA
EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
NULL;
-- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN
deadlock_detected THEN
NULL;
-- handle the error
END;
/
raise_application_error(-20101,
'Expecting at least 1000 tables');
Exceptions Are Raised
Raise
is a statement.
Internal
exceptions are raised
implicitly by the run-time system,
PL/SQL
Exceptions Propagate
When
an exception is raised, if PL/SQL cannot find a handler for it in the current
block or subprogram, the exception propagates.
That
is, the exception reproduces itself in successive enclosing blocks until a
handler is found or there are no more blocks to search. If no handler is found,
PL/SQL returns an unhandled exception error to the
host
environment.
Raised PL/SQL Exceptions:
When
an exception is raised, normal execution of your PL/SQL block or subprogram
stops and control transfers to its exception-handling part,
SQLCODE and SQLERRM
SQLCODE
returns that error number and SQLERRM returns the corresponding error message.
You
cannot use SQLCODE or SQLERRM directly in a SQL statement. Instead, you must
assign their values to local variables, then use the variables in the SQL
statement,
Comments
Post a Comment