This content has been marked as final. Show 5 replies
hello1 person found this helpful
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.
Try giving an explicit grant to the user:
GRANT CREATE SEQUENCE TO OWNER;
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.
I didnt grant any extra rights, tested it first.
privileges granted through roles are not valid in PL/SQL.1 person found this helpful
Try giving the explicit grant...
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!