How to's

[krishnaindia2007]

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.

 

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.

 

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;