SQL Server developer working on Oracle so apologies in advance if I'm fundamentally coming at this incorrectly from an Oracle perspective.

I've got a script I've written which:

  • Checks ALL_SEQUENCES to see if a sequence of a given name exists
  • If it doesn't creates it using EXECUTE IMMEDIATE
  • Attempts to use the sequence as part of an insert

The idea is that the script can be run multiple times without modification so the person running it doesn't have to know what's in place and what's not.

My issue is that in SQL Developer if the sequence doesn't exist, the script errors on running claiming that the sequence doesn't exist. Create the sequence and the error goes away but that rather defeats the point.

What's the best way around this?


Something like this:

   counter integer;
   select count(*) 
      into counter
   from user_sequences
   where sequence_name = 'MY_SEQUENCE';

   if (counter = 0) then 
      execute immediate 'create sequence my_sequence';
   end if;

insert into my_table (id, some_col)
(my_sequence.nextval, 42)

EDIT: If the insert is in the same block as the create then it will need to be done as an execute immediate as well else the Sequence won't be recognised. If they're in separate blocks then it should work fine.

  • That's roughly what I've got, however my insert is in the same begin end block. I'm guessing that's the issue? Dec 19 '11 at 13:59
  • What do you mean with "issue"? What error message do you get? Dec 19 '11 at 15:33
  • 1
    The issue is that block can't validate the existence of my_sequence, so it won't run at all. To have these in the same block you will need to put the insert into an EXECUTE IMMEDIATE as well. Dec 19 '11 at 15:39
  • @a_horse_with_no_name - the issue is in the original question, it claims that the sequence doesn't exist Dec 19 '11 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.