How to's

How to continute after exception is raised?

This is the program to accept two employee numbers from user and display their names and salary details.

If I gave wrong employee number for the first employee it will raise exception and control goes to exception handling part. You can't return from exception handling part to current block and You can't execute second query eventhough second employee given was correct.

Here is the example.

[code]

declare
v_empno1 emp.empno%type := &empno1;
v_empno2 emp.empno%type := &empno2;
v_ename emp.ename%type; v_sal emp.sal%type;

begin

select ename, sal
into v_ename, v_sal
from emp
where empno = v_empno1;

dbms_output.put_line('employee ' || v_ename || ' salary is ' || v_sal);

select ename, sal
into v_ename, v_sal
from emp
where empno = v_empno2;

dbms_output.put_line('employee ' || v_ename || ' salary is ' || v_sal);

exception

when no_data_found then
dbms_output.put_line('employee number not found');

end;
[/code]

To over come the above problem write the above code as follows.
Place the statement in its own sub-block with its own exception handlers. If an error occurs in the sub-block, a local handler can catch the exception. When the sub-block ends, the enclosing block continues to execute at the point where the sub-block ends.

[code]

declare

v_empno1 emp.empno%type := &empno1;
v_empno2 emp.empno%type := &empno2;
v_ename emp.ename%type;
v_sal emp.sal%type;

begin

begin

select ename, sal
into v_ename, v_sal
from emp
where empno = v_empno1;

dbms_output.put_line('employee ' || v_ename || ' salary is ' || v_sal);

exception

when no_data_found then
dbms_output.put_line('employee number ' || v_empno1 || ' not found');

end;

begin

select ename, sal
into v_ename, v_sal
from emp
where empno = v_empno2;

dbms_output.put_line('employee ' || v_ename || ' salary is ' || v_sal);

exception

when no_data_found then
dbms_output.put_line('employee number ' || v_empno2 || ' not found');

end;

end;
[/code]

[b][u] Modifying Complex views using Instead of Triggers[/u][/b]

I am creating a complex view using emp and dept tables.

[code]
sql> create view emp_dept
2 as select a.empno,a.ename,a.sal,b.deptno, b.dname
3 from emp a, dept b
4 where a.deptno = b.deptno;

view created.

Sql> insert into emp_dept values (7456,'krishna',10000,50 ,'purchases');
insert into emp_dept values (7456,'krishna',10000,50 ,'purchases')
* error at line 1:
ora-01776: cannot modify more than one base table through a join view
[/code]

General rule is any insert, update, or delete operation on a join view can modify only one underlying base table at a time. Here we are inserting values into two base tables emp, dept. So it raised error ora-01776.

We may overcome this problem using Instead of triggers.
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through UPDATE, INSERT, and DELETE statemets.

[code]
Create or replace trigger emp_dept_insert
instead of insert on emp_dept
referencing new as n
for each row

declare
rowcnt number;

begin
select count(*) into rowcnt
from emp
where empno = :n.empno;

if rowcnt = 0 then
insert into emp (empno,ename,sal ) values (:n.empno, :n.ename , :n.sal );
else
update emp
set emp.ename = :n.ename, emp.sal = :n.sal
where emp.empno = :n.empno;
end if;

select count(*) into rowcnt
from dept
where deptno = :n.deptno;

if rowcnt = 0 then
insert into dept (deptno, dname) values(:n.deptno, :n.dname);
else
update dept
set dept.dname = :n.dname
where dept.deptno = :n.deptno;
end if;

end;
/

[/code]

sql> insert into emp_dept values (7456,'krishna',10000,50 ,'purchases');
1 row created.

[B][U]Database Trigger execution order[/U][/B]



[CODE]
create table test_11(A NUMBER, B NUMBER);

create or replace TRIGGER tr_before_tab_inst before insert on test_11
begin
dbms_output.put_line('Before insert on table');
end;

create or replace TRIGGER tr_after_tab_inst after insert on test_11
begin
dbms_output.put_line('After insert on table');
end;

create or replace TRIGGER tr_before_row_inst before insert on test_11
for each row
begin
dbms_output.put_line('before insert on row');
end;

create or replace TRIGGER tr_after_row_inst after insert on test_11
for each row
begin
dbms_output.put_line('after insert on row');
end;

SQL> insert into test_11 values(1,1);
Before insert on table
before insert on row
after insert on row
After insert on table

1 row created.

[/CODE]