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?
Best Answer
DECLAREv_dummy NUMBER;BEGIN-- try to find sequence in data dictionarySELECT 1INTO v_dummyFROM user_sequencesWHERE sequence_name = 'MY_SEQUENCE_NAME';-- if sequence found, do nothingEXCEPTIONWHEN no_data_found THEN-- sequence not found, create itEXECUTE IMMEDIATE 'create sequence my_sequence_name';END;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 errorsWHENEVER SQLERROR EXIT SQL.SQLCODEBe aware if there are other errors (permission problems, syntax failures, etc.) in the create sequence statements they will be ignored
I like:
DECLAREC NUMBER;BEGINSELECT COUNT(*) INTO CFROM ALL_TRIGGERSWHERE OWNER = 'YOUROWNER'AND TRIGGER_NAME = 'YOURTRIGGER';IF (C = 0) THENEXECUTE IMMEDIATE 'CREATE TRIGGER "YOUROWNER"."YOURTRIGGER"blah blah blah your trigger blah blah';END IF;END;/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 booleanbeginfor 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 outexecute immediate 'drop sequence '||p_seq_name;execute immediate 'create sequence '||p_seq_name||' start with 1 incrementby 1 nocache';end loop;return true;exceptionwhen others thenreturn false;end;You can parametrize all other options and have a elaborate function to create sequence for you.
DECLARElsSeqName VARCHAR2(32 CHAR) := UPPER('MY_SEQUENCE_NAME');lnSeqCount NUMBER;BEGIN-- try to find sequence in data dictionarySELECT count(1)INTO lnSeqCountFROM user_sequencesWHERE UPPER(sequence_name) = lsSeqName;-- if sequence not found, create itIF lnSeqCount = 0 THENEXECUTE IMMEDIATE 'CREATE SEQUENCE ' || lsSeqName || ' START WITH 1 MINVALUE 1 MAXVALUE 1000000000000000 INCREMENT BY 1 NOCYCLE CACHE 20 NOORDER';END IF;END;/OR
-- helper methodPROCEDURE createSeqIfNotExists (isSeqName VARCHAR2) ISlnSeqCount NUMBER;BEGIN-- try to find sequence in data dictionarySELECT count(1)INTO lnSeqCountFROM user_sequencesWHERE UPPER(sequence_name) = UPPER(isSeqName);-- if sequence not found, create itIF lnSeqCount = 0 THENEXECUTE IMMEDIATE 'CREATE SEQUENCE ' || UPPER(isSeqName) || ' START WITH 1 MINVALUE 1 MAXVALUE 1000000000000000 INCREMENT BY 1 NOCYCLE CACHE 20 NOORDER';END IF;END createSeqIfNotExists;-- call methodBEGINcreateSeqIfNotExists('MY_SEQUENCE_NAME');END;/