Results 1 to 3 of 3

Thread: How to's

  1. #1
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

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


    Last edited by krishnaindia2007; 06-15-2008 at 02:06 AM.

  2. #2
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: How to's

    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.


  3. #3
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: How to's

    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.



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact