Is there something like "if not exist create sequence ..." in Oracle SQL?

Question

For my application that uses an Oracle 8 DB, I am providing an SQL script to setup stuff like triggers, sequences etc., which can be copied and pasted into SQL*Plus. I would like the script to not stop with an error if a sequence that I am trying to create already exists. For a Trigger this can easily be done using "create or replace trigger ...", but for a sequence this does not work. I also tried ""if not exists mysequence then create sequence ..." but it did not too. Is there some alternative?

Alternatively, if this is not possible, is there a way to do a "drop sequence mysequence" without SQL*Plus aborting the script if mysequence does not exist?

Answer 1

DECLARE
  v_dummy NUMBER;
BEGIN
  -- try to find sequence in data dictionary
  SELECT 1
  INTO v_dummy
  FROM user_sequences
  WHERE sequence_name = 'MY_SEQUENCE_NAME';

  -- if sequence found, do nothing
EXCEPTION
  WHEN no_data_found THEN
    -- sequence not found, create it
    EXECUTE IMMEDIATE 'create sequence my_sequence_name';
END;

Answer 2

If you're sure the script will always run under SQL*Plus, you can bracket the CREATE SEQUENCE statements with a directive to continue on error:

WHENEVER SQLERROR CONTINUE
-- create sequences here, ignoring errors
WHENEVER SQLERROR EXIT SQL.SQLCODE

Be aware if there are other errors (permission problems, syntax failures, etc.) in the create sequence statements they will be ignored