ERROR HANDLING
PL/SQL implements error handling with exceptions and exception
handlers. Exceptions can be associated with oracle errors or with your own
user-defined errors. By using exceptions and exception handlers, you can make
your PL/SQL programs robust and able to deal with both unexpected and expected
errors during execution.
ERROR TYPES
Ø Compile-time errors
Ø Runtime errors
Errors that occur during the compilation phase are detected by
the PL/SQL engine and reported back to the user, we have to correct them.
Runtime errors are detected by the PL/SQL runtime engine which can
programmatically raise and caught by exception handlers.
Exceptions are designed for run-time error handling, rather than
compile-time error handling.
HANDLING EXCEPTIONS
When exception is raised, control passes to the exception
section of the block. The exception section consists of handlers for some or
all of the exceptions. An exception handler contains the code that is executed
when the error associated with the exception occurs, and the exception is
raised.
Syntax:
EXCEPTION
When exception_name then
Sequence_of_statements;
When
exception_name then
Sequence_of_statements;
When
others then
Sequence_of_statements;
END;
EXCEPTION TYPES
Ø Predefined exceptions
Ø User-defined exceptions
PREDEFINED EXCEPTIONS
Oracle has predefined several exceptions that corresponds to the
most common oracle errors. Like the predefined types, the identifiers of these
exceptions are defined in the STANDARD package. Because of this, they are
already available to the program, it is not necessary to declare them in the
declarative secion.
Ex1:
DECLARE
a number;
b varchar(2);
v_marks number;
cursor c is select * from student;
type t is varray(3) of varchar(2);
va t := t('a','b');
va1 t;
BEGIN
-- NO_DATA_FOUND
BEGIN
select smarks into v_marks from student where sno = 50;
EXCEPTION
when no_data_found then
dbms_output.put_line('Invalid student number');
END;
--
CURSOR_ALREADY_OPEN
BEGIN
open c;
open c;
EXCEPTION
when cursor_already_open then
dbms_output.put_line('Cursor is already opened');
END;
--
INVALID_CURSOR
BEGIN
close c;
open c;
close c;
close c;
EXCEPTION
when invalid_cursor then
dbms_output.put_line('Cursor is already closed');
END;
-- TOO_MANY_ROWS
BEGIN
select smarks into v_marks from student where sno > 1;
EXCEPTION
when too_many_rows then
dbms_output.put_line('Too many values are coming to marks
variable');
END;
--
ZERO_DIVIDE
BEGIN
a := 5/0;
EXCEPTION
when zero_divide then
dbms_output.put_line('Divided by zero - invalid operation');
END;
-- VALUE_ERROR
BEGIN
b := 'saketh';
EXCEPTION
when value_error then
dbms_output.put_line('Invalid
string length');
END;
-- INVALID_NUMBER
BEGIN
insert into student values('a','srinu',100);
EXCEPTION
when invalid_number then
dbms_output.put_line('Invalid number');
END;
-- SUBSCRIPT_OUTSIDE_LIMIT
BEGIN
va(4) := 'c';
EXCEPTION
when subscript_outside_limit then
dbms_output.put_line('Index is greater than the limit');
END;
-- SUBSCRIPT_BEYOND_COUNT
BEGIN
va(3) := 'c';
EXCEPTION
when subscript_beyond_count then
dbms_output.put_line('Index
is greater than the count');
END;
-- COLLECTION_IS_NULL
BEGIN
va1(1) := 'a';
EXCEPTION
when collection_is_null then
dbms_output.put_line('Collection is empty');
END;
--
END;
Output:
Invalid student number
Cursor is already opened
Cursor is already closed
Too many values are coming to marks
variable
Divided by zero - invalid operation
Invalid string length
Invalid number
Index is greater than the limit
Index is greater than the count
Collection is empty
Ex2:
DECLARE
c number;
BEGIN
c := 5/0;
EXCEPTION
when zero_divide then
dbms_output.put_line('Invalid Operation');
when others then
dbms_output.put_line('From
OTHERS handler: Invalid
Operation');
END;
Output:
Invalid Operation
USER-DEFINED EXCEPTIONS
A user-defined exception is an error that is defined by the
programmer. User-defined exceptions are declared in the declarative secion of a
PL/SQL block. Just like variables, exeptions have a type EXCEPTION and scope.
RAISING EXCEPTIONS
User-defined exceptions are raised explicitly via the RAISE
statement.
Ex:
DECLARE
e exception;
BEGIN
raise e;
EXCEPTION
when e then
dbms_output.put_line('e is
raised');
END;
Output:
e is raised
BULIT-IN ERROR FUNCTIONS
SQLCODE AND SQLERRM
Ø SQLCODE returns the current error code, and SQLERRM returns the current error message text;
Ø For user-defined exception SQLCODE returns 1 and SQLERRM returns “user-deifned exception”.
Ø SQLERRM wiil take only negative value
except 100. If any positive value other than 100 returns non-oracle exception.
Ex1:
DECLARE
e exception;
v_dname varchar(10);
BEGIN
-- USER-DEFINED EXCEPTION
BEGIN
raise e;
EXCEPTION
when e then
dbms_output.put_line(SQLCODE || ' ' || SQLERRM);
END;
-- PREDEFINED EXCEPTION
BEGIN
select dname into v_dname from
dept where deptno = 50;
EXCEPTION
when no_data_found then
dbms_output.put_line(SQLCODE || ' ' || SQLERRM);
END;
END;
Output:
1 User-Defined Exception
100 ORA-01403: no data found
Ex2:
BEGIN
dbms_output.put_line(SQLERRM(100));
dbms_output.put_line(SQLERRM(0));
dbms_output.put_line(SQLERRM(1));
dbms_output.put_line(SQLERRM(-100));
dbms_output.put_line(SQLERRM(-500));
dbms_output.put_line(SQLERRM(200));
dbms_output.put_line(SQLERRM(-900));
END;
Output:
ORA-01403: no data found
ORA-0000: normal, successful completion
User-Defined Exception
ORA-00100: no data found
ORA-00500: Message 500 not found; product=RDBMS;
facility=ORA
-200: non-ORACLE exception
ORA-00900: invalid SQL statement
DBMS_UTILITY.FORMAT_ERROR_STACK
Ø The built-in function, like SQLERRM, returns the message associated with the current error.
Ø It differs from SQLERRM in two ways:
Ø Its length is not restricted; it will
return the full error message string.
Ø You can not pass an error code number to
this function; it cannot be used to return the message for a random error code.
Ex:
DECLARE
v number := 'ab';
BEGIN
null;
EXCEPTION
when others then
dbms_output.put_line(dbms_utility.format_error_stack);
END;
Output:
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value
error: character to number conversion error
ORA-06512: at line 2
DBMS_UTILITY.FORMAT_CALL_STACK
This function returns a formatted string showing the execution
call stack inside your PL/SQL application. Its usefulness is not
restricted to error management; you will also find its handy for tracing the
exectution of your code. You may not use this function in exception block.
Ex:
BEGIN
dbms_output.put_line(dbms_utility.format_call_stack);
END;
Output:
----- PL/SQL Call Stack -----
Object_handle
line_number object_name
69760478 2 anonymous block
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
It displays the execution stack at the point where an exception
was raised. Thus , you can call this function with an exception section at the
top level of your stack and still find out where the error was raised deep
within the call stack.
Ex:
CREATE OR REPLACE PROCEDURE P1 IS
BEGIN
dbms_output.put_line('from procedure 1');
raise value_error;
END P1;
CREATE OR REPLACE PROCEDURE P2 IS
BEGIN
dbms_output.put_line('from procedure 2');
p1;
END P2;
CREATE OR REPLACE PROCEDURE P3 IS
BEGIN
dbms_output.put_line('from procedure 3');
p2;
EXCEPTION
when others then
dbms_output.put_line(dbms_utility.format_error_backtrace);
END P3;
Output:
SQL> exec p3
from procedure 3
from procedure 2
from procedure 1
ORA-06512: at "SAKETH.P1", line
4
ORA-06512: at "SAKETH.P2", line
4
ORA-06512: at "SAKETH.P3", line
4
EXCEPTION_INIT PRAGMA
Using this you can associate a named exception with a particular
oracle error. This gives you the ability to trap this error specifically,
rather than via an OTHERS handler.
Syntax:
PRAGMA EXCEPTION_INIT(exception_name,
oracle_error_number);
Ex:
DECLARE
e exception;
pragma exception_init(e,-1476);
c number;
BEGIN
c := 5/0;
EXCEPTION
when e then
dbms_output.put_line('Invalid
Operation');
END;
Output:
Invalid Operation
RAISE_APPLICATION_ERROR
You can use this built-in function to create your own error
messages, which can be more descriptive than named exceptions.
Syntax:
RAISE_APPLICATION_ERROR(error_number,
error_message,, [keep_errors_flag]);
The Boolean parameter keep_errors_flag
is optional. If it is TRUE, the new error is added to the list of
errors already raised. If it is FALSE, which is default, the new error will
replace the current list of errors.
Ex:
DECLARE
c number;
BEGIN
c := 5/0;
EXCEPTION
when zero_divide then
raise_application_error(-20222,'Invalid Operation');
END;
Output:
DECLARE
*
ERROR at line 1:
ORA-20222: Invalid Operation
ORA-06512: at line 7
EXCEPTION PROPAGATION
Exceptions can occur in the declarative, the executable, or the
exception section of a PL/SQL block.
EXCEPTION RAISED IN THE EXECUATABLE SECTION
Exceptions raised in execuatable section can be handled in
current block or outer block.
Ex1:
DECLARE
e exception;
BEGIN
BEGIN
raise e;
END;
EXCEPTION
when e then
dbms_output.put_line('e is
raised');
END;
Output:
e is raised
Ex2:
DECLARE
e exception;
BEGIN
BEGIN
raise e;
END;
END;
Output:
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined
exception
ORA-06512: at line 5
EXCEPTION RAISED IN THE DECLARATIVE SECTION
Exceptions raised in the declarative secion must be handled in
the outer block.
Ex1:
DECLARE
c number(3) := 'abcd';
BEGIN
dbms_output.put_line('Hello');
EXCEPTION
when others then
dbms_output.put_line('Invalid
string length');
END;
Output:
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value
error: character to number conversion error
ORA-06512: at line 2
Ex2:
BEGIN
DECLARE
c number(3) := 'abcd';
BEGIN
dbms_output.put_line('Hello');
EXCEPTION
when others then
dbms_output.put_line('Invalid string length');
END;
EXCEPTION
when others then
dbms_output.put_line('From outer
block: Invalid string length');
END;
Output:
From outer block: Invalid string length
EXCEPTION RAISED IN THE EXCEPTION SECTION
Exceptions raised in the declarative secion must be handled in
the outer block.
Ex1:
DECLARE
e1 exception;
e2 exception;
BEGIN
raise e1;
EXCEPTION
when e1 then
dbms_output.put_line('e1 is
raised');
raise e2;
when e2 then
dbms_output.put_line('e2 is
raised');
END;
Output:
e1 is raised
DECLARE
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined
exception
ORA-06512: at line 9
ORA-06510: PL/SQL: unhandled user-defined
exception
Ex2:
DECLARE
e1 exception;
e2 exception;
BEGIN
BEGIN
raise e1;
EXCEPTION
when e1 then
dbms_output.put_line('e1
is raised');
raise e2;
when e2 then
dbms_output.put_line('e2
is raised');
END;
EXCEPTION
when e2 then
dbms_output.put_line('From
outer block: e2 is raised');
END;
Output:
e1 is raised
From outer block: e2 is raised
Ex3:
DECLARE
e exception;
BEGIN
raise e;
EXCEPTION
when e then
dbms_output.put_line('e is
raised');
raise e;
END;
Output:
e is raised
DECLARE
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined
exception
ORA-06512: at line 8
ORA-06510: PL/SQL: unhandled user-defined
exception
RESTRICTIONS
You can not pass exception as an argument to a subprogram.
DATABASE TRIGGERS
Triggers are similar to procedures or functions in that they are
named PL/SQL blocks with declarative, executable, and exception handling
sections. A trigger is executed implicitly whenever the triggering event
happens. The act of executing a trigger is known as firing the trigger.
RESTRICTIONS ON TRIGGERES
Ø Like packages, triggers must be stored as
stand-alone objects in the database and cannot be local to a block or package.
Ø A trigger does not accept arguments.
USE OF TRIGGERS
Ø Maintaining complex integrity constraints
not possible through declarative constraints enable at table creation.
Ø Auditing information in a table by
recording the changes made and who made them.
Ø Automatically signaling other programs
that action needs to take place when chages are made to a table.
Ø Perform validation on changes being made
to tables.
Ø Automate maintenance of the database.
TYPES OF TRIGGERS
Ø DML Triggers
Ø Instead of Triggers
Ø DDL Triggers
Ø System Triggers
Ø Suspend Triggers
CATEGORIES
Timing -- Before or After
Level -- Row or Statement
Row level trigger fires once for each row affected by the
triggering statement. Row level trigger is identified by the FOR EACH ROW clause.
Statement level trigger fires once either before or after the
statement.
DML TRIGGER SYNTAX
Create or replace trigger <trigger_name>
{Before | after} {insert or update or delete} on <table_name>
[For each row]
[When (…)]
[Declare]
--
declaration
Begin
--
trigger body
[Exception]
-- exception section
End <trigger_name>;
DML TRIGGERS
A DML trigger is fired on an INSERT, UPDATE, or DELETE
operation on a database table. It can be fired either before or after the
statement executes, and can be fired once per affected row, or once per
statement.
The combination of these factors determines the types of the
triggers. These are a total of 12 possible types (3 statements * 2 timing * 2
levels).
STATEMENT LEVEL
Statement level trigger fires only once.
Ex:
SQL> create table statement_level(count varchar(50));
CREATE OR REPLACE TRIGGER
STATEMENT_LEVEL_TRIGGER
after update on student
BEGIN
insert into statement_level values('Statement level fired');
END STATEMENT_LEVEL_TRIGGER;
Output:
SQL> update student set smarks=500;
3 rows updated.
SQL> select * from statement_level;
COUNT
----------------------------
Statement level fired
ROW LEVEL
Row level trigger fires once for each row affected by the
triggering statement.
Ex:
SQL> create table row_level(count varchar(50));
CREATE OR REPLACE TRIGGER ROW_LEVEL_TRIGGER
after update on student
BEGIN
insert into row_level values('Row level fired');
END ROW_LEVEL_TRIGGER;
Output:
SQL> update student set smarks=500;
3 rows updated.
SQL> select * from statement_level;
COUNT
----------------------------
Row level fired
Row level fired
Row level fired
ORDER OF DML TRIGGER FIRING
Ø Before statement level
Ø Before row level
Ø After row level
Ø After statement level
Ex:
Suppose we have a
follwing table.
SQL> select * from student;
NO NAME MARKS
----- ------- ----------
1 a 100
2 b 200
3 c 300
4 d 400
SQL> create table firing_order(order varchar(50));
CREATE OR REPLACE TRIGGER BEFORE_STATEMENT
before insert on student
BEGIN
insert into firing_order values('Before Statement Level');
END BEFORE_STATEMENT;
CREATE OR REPLACE TRIGGER BEFORE_ROW
before insert on student
for each row
BEGIN
insert into firing_order values('Before Row Level');
END BEFORE_ROW;
CREATE OR REPLACE TRIGGER AFTER_STATEMENT
after insert on student
BEGIN
insert into firing_order values('After Statement Level');
END AFTER_STATEMENT;
CREATE OR REPLACE TRIGGER AFTER_ROW
after insert on student
for each row
BEGIN
insert into firing_order values('After Row Level');
END AFTER_ROW;
Output:
SQL> select * from firing_order;
no rows selected
SQL> insert into student values(5,'e',500);
1 row created.
SQL> select * from firing_order;
ORDER
--------------------------------------------------
Before Statement Level
Before Row Level
After Row Level
After Statement Level
SQL> select * from student;
NO NAME MARKS
---- -------- ----------
1 a 100
2
b 200
3 c 300
4 d 400
5 e 500
CORRELATION IDENTIFIERS IN ROW-LEVEL TRIGGERS
Inside the trigger, you can access the data in the row that is
currently being processed. This is accomplished through two correlation
identifiers - :old and :new.
A correlation identifier
is a special kind of PL/SQL bind variable. The colon in front of
each indicates that they are bind variables, in the sense of host variables
used in embedded PL/SQL, and indicates that they are not regular
PL/SQL variables. The PL/SQL compiler will treat them as records of type
Triggering_table%ROWTYPE.
Although syntactically they are treated as records, in reality
they are not. :old and :new are also known as pseudorecords, for this reason.
TRIGGERING STATEMENT :OLD :NEW
-------------------------------------- ----------------------------
-----------------------------------------------
INSERT all fields are NULL. values that will be
inserted
When the statement is completed.
UPDATE original
values for new values that will be
updated
the row before the when the statement is completed.
update.
DELETE
original values before all
fields are NULL.
the row
is deleted.
Ex:
SQL>
create table marks(no number(2) old_marks number(3),new_marks
number(3));
CREATE OR REPLACE TRIGGER OLD_NEW
before insert or update or delete on student
for each row
BEGIN
insert into marks values(:old.no,:old.marks,:new.marks);
END OLD_NEW;
Output:
SQL> select * from student;
NO NAME MARKS
----- ------- ----------
1 a 100
2 b
200
3 c 300
4 d 400
5 e 500
SQL> select * from marks;
no rows selected
SQL> insert into student values(6,'f',600);
1 row created.
SQL> select * from student;
NO NAME
MARKS
---- -------- ----------
1 a 100
2 b 200
3 c 300
4 d 400
5 e 500
6 f 600
SQL> select * from marks;
NO OLD_MARKS NEW_MARKS
---- --------------- ---------------
600
SQL> update student set marks=555 where no=5;
1 row updated.
SQL> select * from student;
NO NAME MARKS
----- ------- ----------
1 a 100
2 b 200
3 c 300
4 d 400
5 e 555
6 f 600
SQL> select * from marks;
NO OLD_MARKS NEW_MARKS
------ ---------------- ---------------
600
5 500 555
SQL> delete student where no = 2;
1 row deleted.
SQL> select * from student;
NO NAME MARKS
---- -------- ----------
1 a 100
3 c 300
4 d 400
5 e 555
6 f 600
SQL> select * from marks;
NO OLD_MARKS NEW_MARKS
----- --------------
----------------
600
5 500 555
2 200
REFERENCING CLAUSE
If desired, you can use the REFERENCING
clause to specify a different name for :old ane :new. This clause is found
after the triggering event, before the WHEN
clause.
Syntax:
REFERENCING [old as old_name] [new as new_name]
Ex:
CREATE OR REPLACE TRIGGER REFERENCE_TRIGGER
before insert or
update or delete on student
referencing old as
old_student new as new_student
for each row
BEGIN
insert into
marks
values(:old_student.no,:old_student.marks,:new_student.marks);
END REFERENCE_TRIGGER;
WHEN CLAUSE
WHEN
clause is valid for row-level triggers only. If present, the trigger body will
be executed only for those rows that meet the condition specified by the WHEN clause.
Syntax:
WHEN trigger_condition;
Where trigger_condition
is a Boolean expression. It will be evaluated for each row. The :new and :old records can be referenced inside trigger_condition as well, but like REFERENCING, the
colon is not used there. The colon is only valid in the trigger body.
Ex:
CREATE OR REPLACE TRIGGER WHEN_TRIGGER
before insert or
update or delete on student
referencing old as
old_student new as new_student
for each row
when
(new_student.marks > 500)
BEGIN
insert into
marks
values(:old_student.no,:old_student.marks,:new_student.marks);
END WHEN_TRIGGER;
TRIGGER PREDICATES
There are three Boolean functions that you can use to determine
what the operation is.
The predicates are
Ø INSERTING
Ø UPDATING
Ø DELETING
Ex:
SQL> create table predicates(operation varchar(20));
CREATE OR REPLACE TRIGGER PREDICATE_TRIGGER
before insert or update or delete on student
BEGIN
if inserting then
insert into predicates values('Insert');
elsif updating then
insert into predicates
values('Update');
elsif deleting then
insert into predicates
values('Delete');
end if;
END PREDICATE_TRIGGER;
Output:
SQL> delete student where no=1;
1 row deleted.
SQL> select * from predicates;
MSG
---------------
Delete
SQL> insert into student values(7,'g',700);
1 row created.
SQL> select * from predicates;
MSG
---------------
Delete
Insert
SQL> update student set marks = 777 where no=7;
1 row updated.
SQL> select * from predicates;
MSG
---------------
Delete
Insert
Update
INSTEAD-OF TRIGGERS
Instead-of triggers fire instead of a DML operation. Also, instead-of triggers can be defined only on
views. Instead-of triggers are used in two cases:
Ø To allow a view that would otherwise not
be modifiable to be modified.
Ø To modify the columns of a nested table
column in a view.
Ex:
SQL> create view emp_dept as select
empno,ename,job,dname,loc,sal,e.deptno from
emp e, dept d where e.deptno = d.deptno;
CREATE OR REPLACE TRIGGER
INSTEAD_OF_TRIGGER
instead of insert on emp_dept
BEGIN
insert into dept1 values(50,'rd','bang');
insert into
emp1(empno,ename,job,sal,deptno)values(2222,'saketh','doctor',8000,50);
END INSTEAD_OF_TRIGGER;
Output:
SQL> insert into emp_dept
values(2222,'saketh','doctor',8000,'rd','bang',50);
SQL> select * from emp_dept;
EMPNO ENAME JOB SAL DNAME LOC DEPTNO
---------- ---------- ------------
----------- ------------- ------------- ----------
7369 SMITH CLERK 800 RESEARCH DALLAS 20
7499 ALLEN SALESMAN 1600
SALES CHICAGO 30
7521 WARD SALESMAN 1250
SALES CHICAGO 30
7566 JONES MANAGER 2975
RESEARCH DALLAS 20
7654 MARTIN SALESMAN
1250 SALES CHICAGO 30
7698 BLAKE MANAGER 2850
SALES CHICAGO 30
7782 CLARK MANAGER 2450
ACCOUNTING NEW YORK 10
7788 SCOTT ANALYST 3000 RESEARCH DALLAS 20
7839 KING PRESIDENT 5000
ACCOUNTING NEW YORK 10
7844 TURNER SALESMAN
1500 SALES CHICAGO 30
7876 ADAMS CLERK 1100 RESEARCH DALLAS 20
7900 JAMES CLERK 950 SALES CHICAGO 30
7902 FORD ANALYST 3000
RESEARCH DALLAS 20
7934 MILLER CLERK 1300 ACCOUNTING NEW YORK
10
2222 saketh doctor 8000 rd bang 50
SQL> select * from dept;
DEPTNO DNAME LOC
----------
---------------- -----------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 rd bang
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO
---------- ----------
--------------- -------- -------------- ---------
--------- ----------
7369
SMITH CLERK 7902 1 7-DEC-80 800 20
7499 ALLEN SALESMAN 7698
20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698
22-FEB-81 1250
500 30
7566 JONES MANAGER 7839
02-APR-81 2975 20
7654 MARTIN SALESMAN
7698 28-SEP-81 1250
1400 30
7698 BLAKE MANAGER
7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839
09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839
KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN
7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
2222 saketh doctor
8000 50
DDL TRIGGERS
Oracle allows you to define triggers that will fire when Data
Definition Language statements are executed.
Syntax:
Create or replace trigger <trigger_name>
{Before | after} {DDL
event} on {database | schema}
[When (…)]
[Declare]
--
declaration
Begin
--
trigger body
[Exception]
--
exception section
End <trigger_name>;
Ex:
SQL> create table my_objects(obj_name varchar(10),obj_type
varchar(10),obj_owner
varchar(10),obj_time date);
CREATE OR REPLACE TRIGGER CREATE_TRIGGER
after create on database
BEGIN
insert into my_objects
values(sys.dictionary_obj_name,sys.dictionary_obj_type,
sys.dictionary_obj_owner, sysdate);
END CREATE_TRIGGER;
Output:
SQL> select * from my_objects;
no rows selected
SQL> create table stud1(no number(2));
SQL> select * from my_objects;
OBJ_NAME
OBJ_TYPE OBJ_OWNER OBJ_TIME
------------- -------------- -------------- ------------
STUD1 TABLE SYS 21-JUL-07
SQL> create sequence ss;
SQL> create view stud_view as select * from stud1;
SQL> select * from my_objects;
OBJ_NAME
OBJ_TYPE OBJ_OWNER OBJ_TIME
-------------- -------------
---------------- -------------
STUD1 TABLE SYS 21-JUL-07
SS SEQUENCE SYS 21-JUL-07
STUD_VIEW VIEW SYS 21-JUL-07
WHEN CLAUSE
If WHEN present, the trigger body will be
executed only for those that meet the condition specified by the WHEN clause.
Ex:
CREATE OR REPLACE TRIGGER CREATE_TRIGGER
after create on database
when (sys.dictionary_obj_type = ‘TABLE’)
BEGIN
insert into my_objects
values(sys.dictionary_obj_name,sys.dictionary_obj_type,
sys.dictionary_obj_owner, sysdate);
END CREATE_TRIGGER;
SYSTEM TRIGGERS
System triggers will fire whenever database-wide event occurs.
The following are the database event triggers. To create system trigger you
need ADMINISTER DATABASE
TRIGGER
privilege.
Ø STARTUP
Ø SHUTDOWN
Ø LOGON
Ø LOGOFF
Ø SERVERERROR
Syntax:
Create or replace trigger <trigger_name>
{Before | after} {Database event} on {database | schema}
[When (…)]
[Declare]
--
declaration section
Begin
--
trigger body
[Exception]
--
exception section
End <trigger_name>;
Ex:
SQL> create table user_logs(u_name varchar(10),log_time timestamp);
CREATE OR REPLACE TRIGGER AFTER_LOGON
after logon on database
BEGIN
insert into user_logs values(user,current_timestamp);
END AFTER_LOGON;
Output:
SQL> select * from user_logs;
no rows selected
SQL> conn saketh/saketh
SQL> select * from user_logs;
U_NAME
LOG_TIME
----------
------------------------------------------------
SAKETH
22-JUL-07 12.07.13.140000 AM
SQL> conn system/oracle
SQL> select * from user_logs;
U_NAME
LOG_TIME
----------
------------------------------------------------
SAKETH
22-JUL-07 12.07.13.140000 AM
SYSTEM
22-JUL-07 12.07.34.218000 AM
SQL> conn scott/tiger
SQL> select * from user_logs;
U_NAME
LOG_TIME
----------
-----------------------------------------------
SAKETH
22-JUL-07 12.07.13.140000 AM
SYSTEM
22-JUL-07 12.07.34.218000 AM
SCOTT
22-JUL-07 12.08.43.093000 AM
SERVERERROR
The SERVERERROR event can be used to track errors that
occur in the database. The error code is available inside the trigger through
the SERVER_ERROR attribute function.
Ex:
SQL> create table my_errors(error_msg varchar(200));
CREATE OR REPLACE TRIGGER
SERVER_ERROR_TRIGGER
after servererror on database
BEGIN
insert into my_errors values(dbms_utility.format_error_stack);
END SERVER_ERROR_TRIGGER;
Output:
SQL> create table ss (no));
create table ss (no))
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> select * from my_errors;
ERROR_MSG
-------------------------------------------------------------
ORA-00922: missing or invalid option
SQL> insert into student values(1,2,3);
insert into student values(1,2,3)
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from my_errors;
ERROR_MSG
-------------------------------------------------------------
ORA-00922: missing or invalid option
ORA-00942: table or view does not exist
SERVER_ERROR ATTRIBUTE FUNCTION
It takes a single number type of argument and returns the error
at the position on the error stack indicated by the argument. The position 1 is
the top of the stack.
Ex:
CREATE OR REPLACE TRIGGER
SERVER_ERROR_TRIGGER
after servererror on database
BEGIN
insert into my_errors values(server_error(1));
END SERVER_ERROR_TRIGGER;
SUSPEND TRIGGERS
This will fire whenever a statement is suspended. This might
occur as the result of a space issue such as exceeding an allocated tablepace
quota. This functionality can be used to address the problem and allow the
operatin to continue.
Syntax:
Create or replace trigger <trigger_name>
after
suspend on {database | schema}
[When (…)]
[Declare]
--
declaration section
Begin
--
trigger body
[Exception]
--
exception section
End <trigger_name>;
Ex:
SQL> create tablespace my_space datafile 'f:\my_file.dbf' size 2m;
SQL> create table student(sno number(2),sname varchar(10))
tablespace my_space;
CREATE OR REPLACE TRIGGER SUSPEND_TRIGGER
after suspend on database
BEGIN
dbms_output.put_line(‘ No room to insert in your tablespace');
END SUSPEND_TRIGGER;
Output:
Insert more rows
in student table then , you will get
No room to insert
in your tablespace
AUTONOMOUS TRANSACTION
Prior to Oracle8i, there was no way in which some SQL operations
within a transaction could be committed independent of the rest of the
operations. Oracle allows this, however, through autonomous transactions. An autonomous transaction is a transaction
that is started within the context of another transaction, known as parent
transaction, but is independent of it. The autonomous transaction can be
committed or rolled back regardless ot the state of the parent transaction.
Ex:
CREATE OR REPLACE TRIGGER AUTONOMOUS_TRANSACTION_TRIGGER
after insert on student
DECLARE
pragma autonomous_transaction;
BEGIN
update student set marks = 555;
commit;
END AUTONOMOUS_TRANSACTION_TRIGGER;
Output:
SQL> select * from student;
NO NA MARKS
----- ----- -- ----------
1 a 111
2 b 222
3 c 300
SQL> insert into student
values(4,'d',444);
SQL> select * from student;
NO NA MARKS
---- ------ -- ----------
1 a 555
2 b 555
3 c 555
4 d 444
RESTRICTIONS ON AUTONOMOUS TRANSACTION
Ø If an autonomous transaction attempts to
access a resource held by the main transaction, a deadlock can occur in you
program.
Ø You cannot mark all programs in a package
as autonomous with a single PRAGMA declaration. You must indicate
autonomous transactions explicity in each program.
Ø To exit without errors from an autonomous
transaction program that has executed at least one INSERT or UPDATE or DELETE, you
must perform an explicit commit or rollback.
Ø The COMMIT and ROLLBACK statements end the active autonomous transaction, but they do
not force the termination of the autonomous routine. You can have multiple COMMIT and/or ROLLBACK statements inside your autonomous block.
Ø You can not rollback to a savepoint set
in the main transaction.
Ø The TRANSACTIONS
parameter in the oracle initialization file specifies the maximum number of
transactions allowed concurrently in a session. The default value is 75 for
this, but you can increase the limit.
MUTATING TABLES
There are restrictions on the tables and columns that a trigger
body may access. In order to define these restrictions, it is necessary to
understand mutating and constraining tables.
A mutating table is table that is currentlty being modified by a
DML statement and the trigger event also DML statement. A mutating table error
occurs when a row-level trigger tries to examine or change a table that is
already undergoing change.
A constraining table is a table that might need to be read from
for a referential integrity constraint.
Ex:
CREATE OR REPLACE TRIGGER MUTATING_TRIGGER
before delete on student
for each row
DECLARE
ct number;
BEGIN
select count(*) into ct from student
where no = :old.no;
END MUTATING_TRIGGER;
Output:
SQL> delete student where no = 1;
delete student where no = 1
*
ERROR at line 1:
ORA-04091: table SCOTT.STUDENT is
mutating, trigger/function may not see it
ORA-06512: at "SCOTT.T", line 4
ORA-04088: error during execution of
trigger 'SCOTT.T'
HOW TO AVOID MUTATING TABLE ERROR ?
Ø By using autonomous transaction
Ø By using statement level trigger
No comments:
Post a Comment