CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
StuartR143
StuartR143

Reputation: 399

Problems with exception in PL/SQL

I am trying to construct an INSERT INTO query that needs some exception handling.

Here's the code so far:

declare
  nvid number := 4561;
  fromMot number;
  toMot number;
begin
  for idx in 1..10 loop
    for fwd in 0..1 loop

      if fwd=0 then fromMot:=idx; toMot:=8; 
      else fromMot:=8; toMot:=idx; 
      End if;

      insert into MOT_BUFFER_TIME(MBT_ID,MBT_NV_ID,MBT_START_MOT_ID,MBT_END_MOT_ID,MBT_BUFFER_TIME)
      values ((select max(mbt.MBT_ID)+1 from MOT_BUFFER_TIME mbt),nvid,fromMot,toMot,600);
      exception
        when dup_val_on_index then 
          UPDATE MOT_BUFFER_TIME set MBT_BUFFER_TIME=600 where MBT_NV_ID=nvid and MBT_START_MOT_ID=fromMot and MBT_END_MOT_ID=toMot;
    end loop;
  end loop;
end;
/

The table inserts some buffer times (all 600) for a number of MBT_START_MOT_ID and MBT_END_MOT_ID combinations. These are n-8 and 8-n, which I have chosen to do with the nested FOR loops.

The issue is that the table already has some entries (usually 8-5 and 5-8) which have a different value that will need changing to 600, and while MBT_ID is the primary key, there is a constraint that the combination of MBT_NV_ID, MBT_START_MOT_ID and MBT_END_MOT_ID needs to be unique.

So I thought that I would write an exception, such that if there is a duplicate error, I would update instead.

The problem is that that PL/SQL developer is complaining about the placement of exception, telling me that it is expecting something else, and also complaining about unexpected loop on the penultimate line.

Clearly I've done something wrong, but I can't see what. Any hints? Thanks.

EDIT: Added "+1" to the first field in the values section, because without it it was pulling off a primary key value that was already used.

Upvotes: 0

Views: 164

Answers (1)

Hilarion
Hilarion

Reputation: 870

Apart from actually avoiding the problem (e.g. as OldProgrammer suggested), the EXCEPTION statement should be a part of a code block, which you do not have, so you have to add BEGIN and END. Something like this:

declare
  nvid number := 4561;
  fromMot number;
  toMot number;
begin
  for idx in 1..10 loop
    for fwd in 0..1 loop

      if fwd=0 then fromMot:=idx; toMot:=8; 
      else fromMot:=8; toMot:=idx; 
      End if;

      BEGIN
        insert into MOT_BUFFER_TIME(MBT_ID,MBT_NV_ID,MBT_START_MOT_ID,MBT_END_MOT_ID,MBT_BUFFER_TIME)
        values ((select max(mbt.MBT_ID)+1 from MOT_BUFFER_TIME mbt),nvid,fromMot,toMot,600);
      exception
        when dup_val_on_index then 
          UPDATE MOT_BUFFER_TIME set MBT_BUFFER_TIME=600 where MBT_NV_ID=nvid and MBT_START_MOT_ID=fromMot and MBT_END_MOT_ID=toMot;
      END;
    end loop;
  end loop;
end;
/

Edited to match the question edit (as pointed out in the comment).

Upvotes: 3

Related Questions