Using a sequence created in the same script in Oracle 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:

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.