This content has been marked as final. Show 5 replies
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.
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:
I'll have the DBA fix this.
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.
Thanks for your answer max!