Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
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 Answers 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;
share|improve this answer
2  
WHEN OTHERS THEN NULL is not a good idea. Ever. –  Adam Musch Dec 19 '11 at 20:21
    
-1 agree with Adam –  Jack Douglas Dec 19 '11 at 20:39
    
@Jack Please help me by answering question dba.stackexchange.com/questions/9441/… –  bernd_k Dec 20 '11 at 6:59

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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