21

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?

tshepang
  • 12,111
  • 21
  • 91
  • 136
Timo
  • 1,088
  • 1
  • 10
  • 19
  • How would the presence of an AUTOINCREMENT function in Oracle help solve what is clearly a source control/configuration management issue? – APC Apr 11 '10 at 20:08
  • @APC: Sorry, I don't understand. Can you explain this in more detail? What I meant is: all I need for a table is an autoincrement field. In e.g. SQL Server I just define the column to be an autoinc field, and I am done. In Oracle I need to create a numeric column, then a sequence, then a trigger to use that sequence to fill my column. This does not seem very straigforward to me. – Timo Apr 11 '10 at 20:33
  • My point is, if you had proper schema management in place you wouldn't need to handle the prior existence of a sequence because you would only be running a CREATE SEQUENCE statement against databases where you knew that sequence didn't exist. – APC Apr 11 '10 at 20:41
  • 1
    Well, I need that script for various customers with different versions of the database as well as for new customers. I could certainly figure out which customer has what schema and selectively create update scripts for them, and also have a separate script for initial setup, but for simplicity I prefer to have one single script that I can use for everyone. Makes things easier for my colleagues from the support department and the technicians doing the installation without any knowledge about Oracle, and for me because I don't need to sort out help calls because someone used the wrong script :-) – Timo Apr 11 '10 at 20:56

5 Answers5

16
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;
jva
  • 2,797
  • 1
  • 26
  • 41
9

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

dpbradley
  • 11,645
  • 31
  • 34
5

I like:

DECLARE
  C NUMBER;
BEGIN
  SELECT COUNT(*) INTO C
  FROM ALL_TRIGGERS
  WHERE OWNER = 'YOUROWNER'
  AND TRIGGER_NAME = 'YOURTRIGGER';

  IF (C = 0) THEN
    EXECUTE IMMEDIATE '
      CREATE TRIGGER "YOUROWNER"."YOURTRIGGER"
        blah blah blah your trigger blah blah
    ';
  END IF;
END;
/
quillbreaker
  • 6,119
  • 3
  • 29
  • 47
  • Much easier to use CREATE OR REPLACE TRIGGER MyTriggerName BEFORE INSERT ON MyTable..... – Oscar May 31 '17 at 13:27
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.

Guru
  • 2,331
  • 6
  • 31
  • 48
  • I must admit I am such a newbie to Oracle, I do not even know where to define that function and how to call it from my SQL script, but my books on Oracle are already on my desk, so your answer will probably help me when I have more knowledge :-) Thanks. – Timo Apr 11 '10 at 21:06
  • 1
    Warning : If you have code (eg packages) using the sequence, this will invalidate them. You may also have locking issues if those packages are in use when you run this code. Oh, and if the sequence already existed and had been used, you'll get duplicate values. – Gary Myers Apr 11 '10 at 22:39
1
DECLARE
  lsSeqName VARCHAR2(32 CHAR) := UPPER('MY_SEQUENCE_NAME');
  lnSeqCount NUMBER;
BEGIN
  -- try to find sequence in data dictionary
  SELECT count(1)
    INTO lnSeqCount
    FROM user_sequences
    WHERE UPPER(sequence_name) = lsSeqName;
  -- if sequence not found, create it
  IF lnSeqCount = 0 THEN
    EXECUTE IMMEDIATE 'CREATE SEQUENCE ' || lsSeqName || ' START WITH 1 MINVALUE 1 MAXVALUE 1000000000000000 INCREMENT BY 1 NOCYCLE CACHE 20 NOORDER';
  END IF;
END;
/

OR

-- helper method
PROCEDURE createSeqIfNotExists (
  isSeqName VARCHAR2
) IS
  lnSeqCount NUMBER;
BEGIN
  -- try to find sequence in data dictionary
  SELECT count(1)
    INTO lnSeqCount
    FROM user_sequences
    WHERE UPPER(sequence_name) = UPPER(isSeqName);
  -- if sequence not found, create it
  IF lnSeqCount = 0 THEN
    EXECUTE IMMEDIATE 'CREATE SEQUENCE ' || UPPER(isSeqName) || ' START WITH 1 MINVALUE 1 MAXVALUE 1000000000000000 INCREMENT BY 1 NOCYCLE CACHE 20 NOORDER';
  END IF;
END createSeqIfNotExists;

-- call method
BEGIN
  createSeqIfNotExists('MY_SEQUENCE_NAME');
END;
/
Ondřej Štěpán
  • 11
  • 2