The below PL/SQL is not working as expected The ask is to delete triggers if it exists. The below is deleting only the first trigger.

BEGIN
  EXECUTE IMMEDIATE 'DROP TRIGGER ' || 'trigger1';
  EXECUTE IMMEDIATE 'DROP TRIGGER ' || 'trigger2';
  EXECUTE IMMEDIATE 'DROP TRIGGER ' || 'trigger3';
 
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -4080 THEN
      RAISE;
    END IF;
END;

CodePudding user response：

If the first or second trigger doesn't exist then you'll drop to the exception handler. It won't then return to the original program flow, even when you ignore the error, and it will never see or attempt the later dynamic calls.

You need an exception handler around each individual drop.

To reduce repetition you could do that in a loop, or with a local procedure:

DECLARE
  PROCEDURE drop_trigger(p_trigger_name user_triggers.trigger_name%type) IS
  BEGIN
    EXECUTE IMMEDIATE 'DROP TRIGGER ' || p_trigger_name;
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -4080 THEN
        RAISE;
      END IF;
  END drop_trigger;
  
BEGIN
  drop_trigger('trigger1');
  drop_trigger('trigger2');
  drop_trigger('trigger3');
END;
/

db<>fiddle

You can also add debugging, with dbms_output, to see what's happening - as in this modified db<>fiddle.