Exception Handling & Handling PL/SQL Errors Q: What is Exception?
In PL/SQL, the user can catch certain runtime errors. Exceptions can be internally defined by Oracle or the user.
Q: What is Exception Handling?
1. Exceptions are used to handle errors that occur in your PL/SQL code. 2. you can use more than one EXCEPTION in the same Block
3. You could always code a NULL statement if no action is to be taken. 4. you can declare variables in the Exception handler
Q: What are the oracle Error Types?
There are three types of exceptions: 1. Predefined Oracle errors
2. Undefined Oracle errors 3. User-defined errors Identifying Exception Types
Q: what are the Exception Types ?
1. Pre-defined Exception 2. Custom Exception
Q: what are the different parts of the exception?
The different parts of the exception. 1. Declare the exception.
2. Raise an exception. 3. Handle the exception. An exception has four attributes:
1. Name provides a short description of the problem. 2. Type identifies the area of the error.
3. Exception Code gives a numeric representation of the exception. 4. Error message provides additional information about the exception.
The predefined divide-by-zero exception has the following values for the attributes: 1. Name = ZERO_DIVIDE
2. Type = ORA (from the Oracle engine) 3. Exception Code = C01476
4. Error message = divisor is equal to zero
How do you write EXCEPTION in the PLSQL BLOCK?
Pre-Defined Exceptions
Exception Oracle Error SQLCODE Value
NO_DATA_FOUND ORA-01403 Single SELECT statement returned no data
TOO_MANY_ROWS ORA-01422 Single SELECT statement returned more than one row of
data.
ZERO_DIVIDE ORA-01476 A program attempts to divide a number by zero.
VALUE_ERROR ORA-06502 An arithmetic, conversion, truncation, or
size-constraint error occurs.
For example, when your program selects a column value
Oracle Complete PLSQL Reference 99
Error Code Prefix Indicates This Exception Type of Error
ORA Core RDBMS errors
PLS PL/SQL errors
FRM Oracle Forms errors
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.)
DUP_VAL_ON_INDEX ORA-00001 A program attempts to store duplicate values in a
database column that is constrained by a unique index.
INVALID_NUMBER ORA-01722 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. ACCESS_INTO_NULL ORA-06530 A program attempts to assign values to the attributes of
an uninitialized object.
CASE_NOT_FOUND ORA-06592 None of the choices in the WHEN clauses of a CASE
statement is selected, and there is no ELSE clause. COLLECTION_IS_NULL ORA-06531 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.
INVALID_CURSOR ORA-01001 A program attempts a cursor operation that is not
allowed, such as closing an unopened cursor.
LOGIN_DENIED ORA-01017 A program attempts to log on to Oracle with an invalid
username or password.
NOT_LOGGED_ON ORA-01012 A program issues a database call without being
connected to Oracle.
PROGRAM_ERROR ORA-06501 PL/SQL has an internal problem
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
SYS_INVALID_ROWID ORA-01410 The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.
TIMEOUT_ON_RESOURCE ORA-00051 A time-out occurs while Oracle is waiting for a resource
Pre-Defined Exceptions
Exception Oracle Error SQLCODE Value
SUBSCRIPT_BEYOND_COUNT ORA-06533 A program references a nested table or varray element using an index number larger than the number of elements in the collection.
ROWTYPE_MISMATCH ORA-06504 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.
SELF_IS_NULL ORA-30625 A program attempts to call a MEMBER method, but the
Instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is Always the first parameter passed to a MEMBER method.
STORAGE_ERROR ORA-06500 PL/SQL runs out of memory or memory has been
Corrupted.
CURSOR_ALREADY_OPEN ORA-06511 A 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.
OTHERS 1. Any other error occurred.
2. You can use the OTHERS exception to handle all exceptions
3. Because OTHERS handles all exceptions, you must list it after any specific exceptions in your EXCEPTION block.
Create the following Employee and Department tables to be used in the following exercises
when NO_DATA_FOUND then
Raised when Single SELECT statement returned no data
When TOO_MANY_ROWS then
Raised when Single SELECT statement returned more than one row of data.
Oracle Complete PLSQL Reference 102
System Error User Exception
System Error
when NO_DATA_FOUND & TOO_MANY_ROWS then
when ZERO_DEVIDE then
Raised when an attempt is made to divide a number by zero.
when DUP_VAL_ON_INDEX then
Raised when an attempt is made to store duplicate values in a column that is constrained by a unique index
Oracle Complete PLSQL Reference 104
More than one Exception
System Error
User ExceptionUser Exception System Error System Error
when INVALID_NUMBER then
Raised when an attempt is made to convert an invalid character string into a number.
Oracle Complete PLSQL Reference 105
System Error User Exception
when OTHERS then
You can use the OTHERS exception to handle all exceptions
when OTHERS then
Oracle Complete PLSQL Reference 106
System Error
User Exception
OTHERS Exception is not the last one
when OTHERS then
Declare Variables in the Exception Handler
Oracle Complete PLSQL Reference 107
Use more than one nested block in the main block
Use SQLCODE and SQLERRM Functions with Exceptions SQLCODE Return Error code ot Error number for any Error
SQLERRM Return Error Message for any Error
User Defined Exception
1. Is a user defined exception that can be used instead of the pre-defined exception
2. user-defined exceptions must be declared and must be raised explicitly by RAISE statements. 3. Exceptions can be declared only in the declarative part of a PL/SQL block,
4. You declare an exception by introducing its name, followed by the keyword EXCEPTION
5. You cannot declare an exception twice in the same block
6. Exceptions declared in a block are considered local to that block and global to all its sub-blocks. Because a block can reference only local or global exceptions, enclosingblocks cannot reference exceptions declared in a sub-block
User Defined Exception
Defining Your Own Error Messages by using Procedure: RAISE_APPLICATION_ERROR
1.The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions. 2. error_number is a negative integer in the range -20000 .. -20999 and message is a character string up to 2048bytes long
OUT Parameter
1. OUT parameter acts like a variable. You can change its value, and reference the value after assigning it: 2. can be bused when pass parameter values from one procedure to another procedure
2.1 OUT parameter returns a value to the caller of a subprogram.
3. You must pass a variable, not a constant or an expression, to an OUT parameter. 4. OUT formal parameters are initialized to NULL
OUT Parameter
IN OUT Parameter
Call procedure from another procedure
Oracle Complete PLSQL Reference 114
Call External Procedure named Rep1
Call procedure from another procedure
Table Type
SQL> DECLARE
2 TYPE emp_table_struct IS TABLE OF emp.fname%TYPE INDEX BY BINARY_INTEGER;
3 emp_table emp_table_struct;
4 CURSOR emp_cursor IS SELECT fname FROM emp ORDER BY id;
5 v_row NUMBER := 1;
6 BEGIN
7 OPEN emp_cursor;
8 LOOP
9 FETCH emp_cursor INTO emp_table(v_row);
10 EXIT WHEN emp_cursor%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(emp_table(v_row));
12 v_row := v_row + 1;
13 END LOOP;
14 CLOSE emp_cursor;
15 DBMS_OUTPUT.PUT_LINE('Total rows: '||emp_table.COUNT);
16 END;
17 /
DataBase Trigger