Oracle Procedure- Statements outside of End Loop not Executing-CodePudding

Need help with below. statements after end loop not executing. Structure is as follows:

Create or replace procedure a.xyz (b in varchar2,c in varchar2.....) is
  bunch of variable declaration

  cursor c1

begin
  open c1;

  loop
    fetch c1 into ....;
    exit when c1%notfound;

    insert
    insert
    merge
    merge
    commit;
  end loop;

  insert
  select into
  send email
exception
end;

insert, select into, send email not getting executed. Any leads?

CodePudding user response：

You didn't post interesting parts of the procedure - what EXCEPTION does?

This is your pseudocode, modified. Read comments I wrote.

Create or replace procedure a.xyz (b in varchar2,c in varchar2.....) is
  bunch of variable declaration

  cursor c1

begin
  open c1;
  loop
    begin                   --> inner begin - exception - end block
      fetch c1 into ....;
      exit when c1%notfound;

      insert
      insert
      merge
      merge
    exception
      when ... then ...     --> handle exceptions you expect. If you used
                            --  WHEN OTHERS THEN NULL, that'a usually a huge mistake.
                            --  Never use unless you're testing something,
                            --  without RAISE, or if you really don't care if
                            --  something went wrong
    end;                    --> end of inner block
  end loop;

  insert
  select into
  send email

  commit;                   --> commit should be out of the loop; even better,
                            --  you should let the CALLER to decide when and
                            --  whether to commit, not the procedure itself
exception
  when ... then ...
end;

Inner BEGIN-EXCEPTION-END block - if exceptions are properly handled - will let the LOOP end its execution. You should log the error you got (for testing purposes, it could be even

EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line(c1.id ||': '|| sqlerrm);
END;

so that you'd actually see what went wrong. If it were just

EXCEPTION
  WHEN OTHERS THEN NULL;
END;

you have no idea whether some error happened, where nor why.