===================
Exception Handling
===================
PL/SQL Exception message consists of three parts.
1) Type of Exception
2) An Error Code
3) A message
By Handling the exceptions we can ensure a PL/SQL block does not exit abruptly.
The General Syntax for coding the exception section
DECLARE
Declaration section
BEGIN
Exception section
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN
-Error handling statements
END;
General PL/SQL statments can be used in the Exception Block.
If there are nested PL/SQL blocks like this.
DELCARE
Declaration section
BEGIN
DECLARE
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
EXCEPTION
Exception section
END;
In the above case, if the exception is raised in the inner block it should be handled in the exception block of the inner PL/SQL block else the control moves to the Exception block of the next upper PL/SQL Block.
There are 3 types of Exceptions.
a) Named System Exceptions - System exceptions are automatically raised by Oracle, when a program violates a RDBMS rule. There are some system exceptions which are raised frequently, so they are pre-defined and given a name in Oracle which are known as Named System Exceptions. Named system exceptions are:
1) Not Declared explicitly,
2) Raised implicitly when a predefined Oracle error occurs,
3) caught by referencing the standard name within an exception-handling routine.
CURSOR_ALREADY_OPEN (ORA-06511) When you open a cursor that is already open.
INVALID_CURSOR (ORA-01001) When you perform an invalid operation on a cursor like closing a cursor, fetch data from a cursor that is not opened.
NO_DATA_FOUND (ORA-01403) When a SELECT...INTO clause does not return any row from a table.
TOO_MANY_ROWS (ORA-01422) When you SELECT or fetch more than one row into a record or variable.
ZERO_DIVIDE (ORA-01476) When you attempt to divide a number by zero.
BEGIN
Execution section
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line ('A SELECT...INTO did not return any row.');
END;
b) Unnamed System Exceptions - Those system exception for which oracle does not provide a name is known as unamed system exception. These exception do not occur frequently. These Exceptions have a code and an associated message.
There are two ways to handle unnamed sysyem exceptions:
1. By using the WHEN OTHERS exception handler, or
2. By associating the exception code to a name and using it as a named exception. We can assign a name to unnamed system exceptions using a Pragma called EXCEPTION_INIT. EXCEPTION_INIT will associate a predefined Oracle error number to a programmer_defined exception name.
CREATE OR REPLACE PROCEDURE EXCEPTION_PROC
IS
UN_NAMED_EXCEPTION EXCEPTION;
PRAGMA
EXCEPTION_INIT (UN_NAMED_EXCEPTION, -00022);
BEGIN
RAISE UN_NAMED_EXCEPTION;
EXCEPTION
WHEN UN_NAMED_EXCEPTION THEN
dbms_output.put_line( '' || 'UN_NAMED_EXCEPTION');
END;
c) User-defined Exceptions
Apart from sytem exceptions we can explicity define exceptions based on business rules. These are known as user-defined exceptions.
Steps to be followed to use user-defined exceptions:
A)They should be explicitly declared in the declaration section.
B)They should be explicitly raised in the Execution Section.
C)They should be handled by referencing the user-defined exception name in the exception section.
RAISE_APPLICATION_ERROR ( ) - a built-in procedure in oracle which is used to display the user-defined error messages along with the error number whose range is in between -20000 and -20999. Whenever a message is displayed using RAISE_APPLICATION_ERROR, all previous transactions which are not committed within the PL/SQL Block are rolled back automatically. RAISE_APPLICATION_ERROR raises an exception but does not handle it.
RAISE_APPLICATION_ERROR is used for the following reasons,
a) to create a unique id for an user-defined exception.
b) to make the user-defined exception look like an Oracle error.
RAISE_APPLICATION_ERROR (error_number, error_message);
• The Error number must be between -20000 and -20999
• The Error_message is the message you want to display when the error occurs.
Steps to be folowed to use RAISE_APPLICATION_ERROR procedure:
1. Declare a user-defined exception in the declaration section.
2. Raise the user-defined exception based on a specific business rule in the execution section.
3. Finally, catch the exception and link the exception to a user-defined error number in RAISE_APPLICATION_ERROR.
CREATE OR REPLACE PROCEDURE EXCEPTION_PROC_RAE
IS
UN_NAMED_EXCEPTION EXCEPTION;
BEGIN
RAISE UN_NAMED_EXCEPTION;
EXCEPTION
WHEN UN_NAMED_EXCEPTION THEN
raise_application_error(-20000, 'Raising un named exception');
END;
Output :-
SQL> exec EXCEPTION_PROC_RAE
BEGIN EXCEPTION_PROC_RAE; END;
*
ERROR at line 1:
ORA-20000: Raising un named exception
ORA-06512: at "IPHSIT.EXCEPTION_PROC_RAE", line 11
ORA-06512: at line 1
==========
Trigger
==========
A trigger is triggered automatically when an associated DML statement is executed.
CREATE [OR REPLACE ] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition
BEGIN
--- sql statements
END;
a) {BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.
b) {INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
c) [OF col_name] - This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
d) [ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.
e) REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as :old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
f) [FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).
g) WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.
No comments:
Post a Comment