PL/SQL WHEN OTHERS THEN error

Question

I am writing a simple procedure which should INSERT a row in a table and UPDATE all others with the same foreign key afterwards. My problem is WHEN OTHERS THEN statement. SQL Developer is giving me this error:

Error(26,3): PLS-00103: Encountered the symbol "WHEN" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge The symbol "exception" was substituted for "WHEN" to continue.

My procedure is as follows:

CREATE OR REPLACE PROCEDURE INS_RETUR 
(
  P_BRDOKUMENTA IN RETUR.BrDokumenta%TYPE, 
  P_DATKREIRANJA IN RETUR.DatKreiranja%TYPE,
  P_DATSTAMPANJA IN RETUR.DatStampanja%TYPE,
  P_SIFRJ IN RETUR.SifRJ%TYPE,
  P_BRRADKNJIZ IN RETUR.BrRadKnjiz%TYPE,
  P_PRIJEMNIBR IN RETUR.PrijemniBr%TYPE,
  P_BRPORUDZBENICE IN RETUR.BrPorudzbenice%TYPE,

  P_REZULTAT OUT NUMBER
)
AS 
BEGIN
  P_REZULTAT := 0;
  P_PORUKA := 'Ok';

  INSERT INTO Retur
  VALUES (P_BRDOKUMENTA, P_DATKREIRANJA, P_DATSTAMPANJA, P_SIFRJ, P_BRRADKNJIZ, P_PRIJEMNIBR, P_BRPORUDZBENICE, 'A');

  UPDATE Retur
  SET Status = 'N'
  WHERE BrPorudzbenice = P_BRPORUDZBENICE
  AND BrDokumenta != P_BRDOKUMENTA;

  WHEN OTHERS THEN
    P_REZULTAT := 1;
    P_PORUKA := (-10001,'Doslo je do greske - '||SQLCODE||' -GRESKA- '||SQLERRM);
END INS_RETUR;

Why is this happening?

Also - do I have to RAISE this error in order to rollback, or this will be done automatically when code reaches WHEN OTHERS THEN statement? Thanks in advance.

Answer 1

You are missing an EXCEPTION:

BEGIN
...
EXCEPTION
  WHEN OTHERS THEN
    P_REZULTAT := 1;
    ...
END;

The error message is useful in this case:

Error(26,3): PLS-00103: Encountered the symbol "WHEN" when expecting one of the following: ( begin case declare end exception exit for goto if loop ...