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;