This discussion is archived
5 Replies Latest reply: Jun 30, 2011 2:31 AM by 866990 RSS

ORA-02289 drop and create sequence using execute immediate

866990 Explorer
Currently Being Moderated
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
  • 1. Re: ORA-02289 drop and create sequence using execute immediate
    32685 Expert
    Currently Being Moderated
    hello

    The user that owns the procedure needs to have permissions granted to allow it to create and drop sequences - that would explain why you get the ORA-01031: insufficient privileges exception...

    grant create any sequence to <package owner>
    /
    grant drop any sequence to <package owner>
    /

    But bear in mind that this user will now have permissions to drop any sequence which isn't great.

    HTH

    David
  • 2. Re: ORA-02289 drop and create sequence using execute immediate
    730428 Guru
    Currently Being Moderated
    Try giving an explicit grant to the user:
    GRANT CREATE SEQUENCE TO OWNER;
    Max
  • 3. Re: ORA-02289 drop and create sequence using execute immediate
    866990 Explorer
    Currently Being Moderated
    When I create the sequence manually in sqlplus it works. (as the same user, OWNER)

    SQL> drop sequence sq1;
    Reeks is verwijderd.
    SQL> create sequence sq1 start with 1;
    Reeks is aangemaakt.
    SQL>

    I didnt grant any extra rights, tested it first.
  • 4. Re: ORA-02289 drop and create sequence using execute immediate
    730428 Guru
    Currently Being Moderated
    privileges granted through roles are not valid in PL/SQL.
    Try giving the explicit grant...

    Max
  • 5. Re: ORA-02289 drop and create sequence using execute immediate
    866990 Explorer
    Currently Being Moderated
    Max, thanks for your answer.
    Rights have been given to the user through a role, indeed.
    I've done a little test, which clearly shows what is going on:
    SQL> create sequence sq1 start with 1;
    
    Reeks is aangemaakt.
    
    SQL> create or replace procedure refresh_sq1 as
      2  begin
      3  EXECUTE IMMEDIATE('drop sequence sq1');
      4   EXECUTE IMMEDIATE('create sequence sq1 start with 1');
      5   end;
      6  /
    
    Procedure is aangemaakt.
    
    SQL> exec refresh_sq1;
    BEGIN refresh_sq1; END;
    
    *
    FOUT in regel 1:
    .ORA-01031: insufficient privileges
    ORA-06512: at "OWNER.REFRESH_SQ1", line 4
    ORA-06512: at line 1
    
    SQL> drop sequence sq1;
    drop sequence sq1
                  *
    FOUT in regel 1:
    .ORA-02289: sequence does not exist
    
    SQL> create sequence sq1 start with 1;
    
    Reeks is aangemaakt.
    
    SQL> 
    SQL> begin
      2  EXECUTE IMMEDIATE('drop sequence sq1');
      3   EXECUTE IMMEDIATE('create sequence sq1 start with 1');
      4   end;
      7  /
    
    PL/SQL-procedure is geslaagd.
    I'll have the DBA fix this.
    Thanks for your answer max!

    Robin

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points