Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

Something like this:

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

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

insert into my_table (id, some_col)
values
(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.

share|improve this answer
    
That's roughly what I've got, however my insert is in the same begin end block. I'm guessing that's the issue? –  Jon Hopkins Dec 19 '11 at 13:59
    
What do you mean with "issue"? What error message do you get? –  a_horse_with_no_name 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. –  Leigh Riffel 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 –  Jon Hopkins Dec 19 '11 at 16:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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