How To Catch Exception Inside Cursor Loop? (Doc ID 1432897.1)

Last updated on MARCH 26, 2012

Applies to:

PL/SQL - Version: 11.1.0.7 and later   [Release: 11.1 and later ]
Information in this document applies to any platform.

Goal

The following procedure failed to compile with the EXCEPTION inside the cursor loop. If commenting out EXCEPTION section, the procedure compiled successfully.

Question: Does Oracle support catch exception inside cursor loop? if yes, could you provide a working example?

Testcase:
=======

create table test (id integer,name varchar2(32));

create unique index idx_u_test on test(id);

create table test1 (id integer,name varchar2(32));

create table t_error (id integer,name varchar2(32));

insert into test values (2,'2');
insert into test values(4,'4');

commit;

insert into test1 values (1,'2');
insert into test1 values (2,'2');
insert into test1 values (3,'2');
insert into test1 values (4,'2');
insert into test1 values (5,'2');
insert into test1 values (6,'2');
insert into test1 values (7,'2');
insert into test1 values (8,'2');
commit;

CREATE OR REPLACE PROCEDURE p_insert
as
  i_id integer;
  s_name varchar2(32);
  exeSql varchar2(200);
  exeSql2 varchar2(200);
  cursor cur_cdr is SELECT id,name FROM test1;
  BEGIN
    OPEN cur_cdr ;
      LOOP
        FETCH cur_cdr INTO i_id,s_name;
        EXIT WHEN cur_cdr%NOTFOUND;
        exeSql := 'insert into test values('||i_id||','||''''||s_name||''''||')';
        DBMS_OUTPUT.put_line(exeSql);
        execute immediate exeSql;
        DBMS_OUTPUT.put_line('Completed: '||exeSql);
        commit;

       EXCEPTION
          WHEN OTHERS THEN
           begin
             exeSql2 := 'insert into t_error values(' ||i_id||','||''''||s_name||''''||')';
             execute immediate exeSql2;
             commit;
           end;

    END LOOP;
    close cur_cdr;
END p_insert;
/

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE P_INSERT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
19/8 PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
of the following:
( begin case declare end exit for goto if loop mod null
pragma raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
continue close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe purge

28/5 PLS-00103: Encountered the symbol "CLOSE" when expecting one of
the following:
end not pragma final instantiable order overriding static

LINE/COL ERROR
-------- -----------------------------------------------------------------
member constructor map

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms