CODEWITHSUNDEEP
oracle-databasestored-procedures
Bigeyes
Bigeyes

Reputation: 1666

Check record exist before update

I want to update a table but I want to check if the record exist. If not then throw an exception. In the C# application I pass the parameters and execute the command by the following.

procedure usp_update_example
(
   p_id            in mydb.member.idn_member%type,
   p_idn_person    in mydb.member.idn_person%type,
   p_ind_rep       in mydb.member.ind_rep%type
) 
as
    v_exist   pls_integer := 0;
    v_step    varchar2(250);
    v_exception_not_exist exception;
begin
    v_step := 'Check for record '  || p_id;
    select count(1)
      into v_exist
      from mydb.member
      where idn_member = p_id;

      if v_exist = 0 then
         raise v_exception_not_exist;
      end if;

      if (v_exist > 0) then
      v_step := 'Update table :' || p_id;
      update mydb.member
      set
         idn_person   =  p_idn_person,
         ind_rep      =  p_ind_rep
      where idn_member = p_id;
      end if;
   exception
   when v_exception_not_exist then
     Raise_application_error(-20001, 'Not exist');
end usp_update_example;

However even my condition is right, I do have the record existing in the table. I always get Not exist exception. If I don't use if v_exist = 0 and use WHEN NO_DATA_FOUND THEN. Then everything is fine.

I am not sure where is wrong.

Upvotes: 0

Views: 63

Answers (1)

Maxim Borunov
Maxim Borunov

Reputation: 911

Your code seems to be fine. Looks like this issue is related to some uncommitted data - you see the record in the session where you inserted it and you don't see it in C# session since the record is not committed yet. Hence, C# session generates exception.

What I would suggest re the procedure code is to make it more compact. Something like the following:

...
begin
  update mydb.member
  set    idn_person   =  p_idn_person,
         ind_rep      =  p_ind_rep
  where idn_member = p_id;

  if SQL%ROWCOUNT = 0 then
    raise_application_error(-20001,'Not exist');
  end if;
end;

Upvotes: 1

Related Questions