5 Replies Latest reply: Jun 30, 2011 4:31 AM by RobbieNerve RSS

    ORA-02289 drop and create sequence using execute immediate

    RobbieNerve
      Hi All,

      i've copied a schema from one database to another.
      In the old database my package is working fine and the procedure inside the package PKG_REFRESH below works like a charm.

      PROCEDURE FILL_TABLE(P_PROC_ID INTEGER) AS
      BEGIN
      EXECUTE IMMEDIATE('drop sequence sq1');
      EXECUTE IMMEDIATE('create sequence sq1 start with 1 cache 500');
      v_kenmerk := 'dim_1';
      INSERT INTO dim_1(
      field1
      , field2
      ...<snip>
      END;

      Now I've imported the package into another database and while all sequences used by procedures inside the package exist, it gives the errors below:

      begin
      *
      FOUT in regel 1:
      .ORA-02289: sequence does not exist
      ORA-06512: at "OWNER.PKG_REFRESH", line 117
      ORA-06512: at "OWNER.PKG_REFRESH", line 80
      ORA-01031: insufficient privileges
      ORA-06512: at "OWNER.PKG_REFRESH", line 20
      ORA-06512: at line 3

      I checked and the sequence is dropped but hasnt been recreated. However it doesnt give me an error message when trying to create the sequence.
      I'm puzzled... search on this forum, google and metalink didnt help me either.

      Does anyone have a clue why this is happening? Strange part is that it does work on the old database.

      Robin

      Edited by: RobbieNerve on 30-jun-2011 10:27 forgot to tell the procedure is inside the package