Is Using Execute Immediate Bad Practice in Oracle?

Question

So, I've been writing a script which creates a sequence (if it doesn't exist) then copies some data including a value from that sequence before deleting the original source data.

As the sequence can't be guaranteed to exist when the script runs, the insert seems (I really don't know Oracle) to have to go in an EXECUTE IMMEDIATE statement.

While this works (abbreviated section of code below), it feels like a horrible cludge. Is this bad practice in Oracle? If so then how should it be done?

DECLARE 
  SequenceCount NUMBER;

BEGIN

  BEGIN

    -- Check to see whether the sequence exists. If not create it, if so do nothing.

    SELECT COUNT(1) INTO SequenceCount FROM ALL_SEQUENCES WHERE SEQUENCE_NAME = 'USER_DEVICE_MAP_BACKUP_SEQ';

    IF SequenceCount < 1 THEN
      IF DebugMode = 1 THEN
        DBMS_OUTPUT.PUT_LINE('Creating Sequence.');
      END IF;

      EXECUTE IMMEDIATE 'CREATE SEQUENCE USER_DEVICE_MAP_BACKUP_SEQ START WITH 1 INCREMENT BY 1';
    END IF;

  END;

  BEGIN

    EXECUTE IMMEDIATE 'INSERT INTO USER_DEVICE_MAP_BACKUP SELECT USER_DEVICE_MAP_BACKUP_SEQ.NEXTVAL, USER_ID, DEVICE_ID, sysdate FROM USER_DEVICE_MAP';

    DELETE FROM USER_DEVICE_MAP;

  END;

END;

Answer 1

Well, it may seem kludgy, but compared to using DBMS_SQL, it's downright elegant.

Answer 2

It is OK to use Execute immediate here. You can shorten the conditional creation to:

BEGIN 
      EXECUTE IMMEDIATE 'CREATE SEQUENCE USER_DEVICE_MAP_BACKUP_SEQ START WITH 1 INCREMENT BY 1';
exception when others then
    Null;
end;