How to find if a sequence exits using PL/SQL

Question

I need to find using PL/SQL if a specific sequence named e.g. MY_SEQ exits. If the sequence exists then drop it and create a new, or else to just create a new sequence.

E.G. (pseudocode)

IF EXISTS(MY_SEQ) THEN
BEGIN
   DROP SEQUENCE MY_SEQ;
   CREATE SEQUENCE MY_SEQ...
END;
ELSE
BEGIN
  CREATE SEQUENCE MY_SEQ;
END;

Answer 1

I have several ideas for this (all untested):

1) Oracle normally supports something like CREATE OR REPLACE

2) Use one of the system views from the SYS user to check with a SELECT whether the Sequence exists.

3) Use execute_immediate with a BEGIN .. EXCEPTION ... END block to drop the object. If it does not exist, an error should occur, which you can ignore.

Answer 2

Hi, you can check the dictionary view ALL_SEQUENCES (or USER_SEQUENCES if the executing user is the owner), for example:

BEGIN
   FOR cc IN (SELECT sequence_name as sequence_exists
                FROM all_sequences
               WHERE sequence_owner = :seq_owner
                 AND sequence_name = :seq_name) LOOP
      -- sequence exists, drop it (at most there will be *one* sequence)
      EXECUTE IMMEDIATE 'DROP SEQUENCE XXX';
   END LOOP;
   -- create sequence
   EXECUTE IMMEDIATE 'CREATE SEQUENCE XXX';
END;