CODEWITHSUNDEEP
oracle-databaseplsql
John Wick
John Wick

Reputation: 745

Is the EXCEPTION in the wrong place? Where is the best place to put it?

So i am trying to create entries by looping through a cursor; however, I am trying to handle an exception (DUP_VAL_ON_INDEX) within the loop. IS this possible? IF so, what am I doing wrong? Below is my code:

create or replace Procedure EY IS
CURSOR c1 is 
  select ey_id , cycle_id
  from mvs v
 cross join cycle s
 l_var c1%ROWTYPE;
   BEGIN
      OPEN c1;
   LOOP
      BEGIN
        FETCH c1 into l_var;
        EXIT when c1%NOTFOUND;
      insert into EY (EY_id, cycle_id, create_dt, create_user_id)
      values (l_var.ey_id, l_var.cycle_id, trunc(sysdate), '1');
        EXCEPTION
            WHEN DUP_VAL_ON_INDEX THEN
            DBMS_OUTPUT.PUT_LINE('duplicate value');  
  END LOOP;
    close c1;
END EY;

Thanks in advance

Upvotes: 1

Views: 39

Answers (1)

Littlefoot
Littlefoot

Reputation: 142710

Yes, that is the way to continue LOOP execution if something happens - enclose loop contents into its own BEGIN-EXCEPTION-END block.

Your code seems to be OK, but it is missing the END statement for the mentioned inner BEGIN-EXCEPTION-END block, i.e.

create or replace Procedure EY IS
  CURSOR c1 is 
    select ey_id , cycle_id
    from mvs v
    cross join cycle s
  l_var c1%ROWTYPE;
BEGIN
  OPEN c1;
  LOOP
    BEGIN
      FETCH c1 into l_var;
      EXIT when c1%NOTFOUND;
      insert into EY (EY_id, cycle_id, create_dt, create_user_id)
        values (l_var.ey_id, l_var.cycle_id, trunc(sysdate), '1');
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
      DBMS_OUTPUT.PUT_LINE('duplicate value');  
    end;                   --> you're missing this END
  END LOOP;
  close c1;
END EY;

If you properly indent code, it is easier to spot the culprit.

Upvotes: 2

Related Questions