2

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;
2
  • 2
    The above code would fail if another user had a sequence with the same name that your user had rights against; your query should use USER_SEQUENCES, not ALL_SEQUENCES. – Adam Musch Dec 19 '11 at 20:23
  • @AdamMusch - Thanks for that, I'll amend accordingly. – Jon Hopkins Dec 19 '11 at 21:15
2

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

0

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;
3

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.