raise Exception inside if condition in plsql-CodePudding

declare
      cnt number; 
begin
      select count(*)
      into cnt
       FROM employee;
       IF cnt = 0 THEN
               DELETE employee;
               COMMIT;
            EXCEPTION
            WHEN others THEN
               log_error_local(k_sub_module, l_step||' '||sqlerrm);
       raise;
       END IF;
    end

getting syntax error like below.. Error(188,13): PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following: ( begin case declare else elsif end 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

CodePudding user response：

To fix your syntax issues, you can wrap the contents of the IF .. THEN statement in a BEGIN/END block:

DECLARE
  cnt number; 
BEGIN
  select count(*)
  into cnt
  FROM employee;
  
  IF cnt = 0 THEN
    BEGIN
      DELETE FROM employee;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        log_error_local(k_sub_module, l_step||' '||sqlerrm);
        raise;
    END;
  END IF;
END;
/

Note: the DELETE statement is missing the FROM keyword and you do not appear to have declared the k_sub_module or l_step variables.

or, since you are re-raising the exception and execution will be halted anyway, catch the exception outside the IF statement in the main block:

DECLARE
  cnt number; 
BEGIN
  select count(*)
  into cnt
  FROM employee;
  
  IF cnt = 0 THEN
    DELETE FROM employee;
    COMMIT;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    log_error_local(k_sub_module, l_step||' '||sqlerrm);
    raise;
END;
/

db<>fiddle here