Home » SQL & PL/SQL » SQL & PL/SQL » Insufficient privs to create sequence from procedure
Insufficient privs to create sequence from procedure [message #225373] Mon, 19 March 2007 16:46 Go to next message
zorro2b
Messages: 2
Registered: March 2007
Junior Member
I am trying to create a sequence from within a PL/SQL procedure (actually a function in a package). When I execute the sql directly as the schema owner it works fine. But when I execute the same via the stored procedure and execute immediate I get an exception with SQLCODE= and SQLERRM="ORA-01031: insufficient privileges
ORA-02289: sequence does not exist".

Is there something special I need to grant to the owning schema to make this work?

Is there something additional I will need to grant to other users to allow them to call the procedure also?

This is the relevant snippet of code from the stored procedure:

    l_sql := 'CREATE SEQUENCE '|| get_seq_name(site) ||
        ' MINVALUE '||l_start_value||' MAXVALUE 2000000000 '||
        'INCREMENT BY 1 '||
        'START WITH '||l_start_value||' CACHE 20 NOORDER CYCLE';
        
    execute immediate l_sql;
Re: Insufficient privs to create sequence from procedure [message #225374 is a reply to message #225373] Mon, 19 March 2007 16:49 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
Privs acquired via ROLE do NOT apply with PL/SQL procedures!
Re: Insufficient privs to create sequence from procedure [message #225463 is a reply to message #225373] Tue, 20 March 2007 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at:
http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html
Regards
Michel
Re: Insufficient privs to create sequence from procedure [message #225474 is a reply to message #225463] Tue, 20 March 2007 01:00 Go to previous message
zorro2b
Messages: 2
Registered: March 2007
Junior Member
Thanks, that was very helpful.
Previous Topic: one time only procedure
Next Topic: Generate a report using PL/SQL
Goto Forum:
  


Current Time: Sun Dec 04 20:31:14 CST 2016

Total time taken to generate the page: 0.15130 seconds