How to's

krishnaindia2007 · #1 (**permalink**) 06-15-2008

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;

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;

krishnaindia2007 · #2 (**permalink**) 06-15-2008

Modifying Complex views using Instead of Triggers

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

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; 
/

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

krishnaindia2007 · #3 (**permalink**) 06-18-2008

Database Trigger execution order

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode