drop and create sequence from a stored procedure

[slimdave]

Look out for name space conflicts ... you are trying to create a sequence called "TEST" when you already have a procedure called "TEST". That'd probably be an issue.

Wrap the 'drop sequence' block in it's own begin .. end block, and use an exception clause to get oracle errors ignored.

CREATE OR REPLACE PROCEDURE my_proc
IS
   l_seq_start_with number := 7;
begin
   begin
      execute immediate 'DROP SEQUENCE MY_SEQ';
   exception
      when others then null;
   end;
   execute immediate
      'CREATE SEQUENCE MY_SEQ START WITH '
         ||to_char(l_seq_start_with)
         ||' INCREMENT BY 1 NOCACHE';
end;
/