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

Question

Very probably a noob 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. Is there some alternative, like "if not exists mysequence then create sequence ..." (I tried this but it did not work :) )

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?

Thanks.

PS: I wish Oracle just had a simple Autoinc field type ... sigh.

Answer 1

You can check user_sequence table to see whether the sequence being created exists already or not.

Similar to davek's solution: The idea is, before creating any sequence, drop the sequence and create it, all in dynamic SQL, create a function, and say when you need to create 10 sequence, let the function take care...

function crt_seq(p_seq_name varchar2)
return boolean
begin
   for i in (select 1 from user_sequence where sequence_name = upper(p_seq_name))
   loop
   ---- Already exists. You can drop and recreate or return false to error out
   execute immediate 'drop sequence '||p_seq_name;
   execute immediate 'create sequence '||p_seq_name||' start with 1 increment
                    by 1 nocache';
   end loop;
   return true;
exception
when others then
   return false;
end;

You can parametrize all other options and have a elaborate function to create sequence for you.

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

Answer 3

DECLARE
  v_dummy NUMBER;
BEGIN
  -- try to find sequence in data dictionary
  SELECT 1
  INTO v_dummy
  FROM user_sequence
  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 4

This worked for me...

declare
    c number;
    begin
        select count(*) into c from user_sequences where sequence_name = 'SMS_RQST_SEQ';
        if c = 0  then
            CREATE SEQUENCE SMS_RQST_SEQ MINVALUE 1 MAXVALUE 999999999 INCREMENT BY 1 START WITH 1 NOCACHE NOORDER NOCYCLE;
        end if;
    end;
/