Oracle PL/SQL - Adding an Exception Handler to a CURSOR FOR Loop

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Cursor
  4. Explicit Cursor FOR LOOP Statement

Description

Adding an Exception Handler to a CURSOR FOR Loop

Demo

SQL>
SQL> drop table emp;

Table dropped.--   ww  w.  j av  a  2 s  .c o  m
SQL>
SQL> create table emp(
  2    empno    number(4,0),
  3    ename    varchar2(10),
  4    job      varchar2(9),
  5    mgr      number(4,0),
  6    hiredate date,
  7    sal      number(7,2),
  8    comm     number(7,2),
  9    deptno   number(2,0)
 10  );

Table created.
SQL>
SQL>
SQL> insert into emp values(7839, 'KING',  'PRESIDENT', null, to_date('17-11-1981','dd-mm-yyyy'), 5000, null, 10);
SQL> insert into emp values(7698, 'BLAKE', 'MANAGER', 7839,to_date('1-5-1981','dd-mm-yyyy'), 2850, null, 30);
SQL> insert into emp values(7782, 'CLARK', 'MANAGER', 7839,to_date('9-6-1981','dd-mm-yyyy'), 2450, null, 10);
SQL> insert into emp values(7566, 'JONES', 'MANAGER', 7839,to_date('2-4-1981','dd-mm-yyyy'), 2975, null, 20);
SQL>
SQL> declare
  2       cursor c_emp (ci_deptNo NUMBER) is
  3          select empNo, deptNo, ename
  4          from emp
  5          where deptNo = ci_deptNo
  6          order by empNo; --helps ID failure point
  7       v_empNo NUMBER;
  8  begin
  9       for r_emp in c_emp(10) loop
 10          v_empNo := r_emp.empNo; --record identifier
 11          --something that could fail #1..>
 12       end loop;
 13  exception
 14       when others then
 15          raise_application_error
 16          (-20999,'Update failed on the emp#'||v_empNo||' with error :'||sqlerrm);
 17  end;
 18  /

PL/SQL procedure successfully completed.
SQL>

Related Topic