Re: stored proc to create sequence, insufficent priv

From: Sybrand Bakker <gooiditweg_at_sybrandb.demon.nl>
Date: Thu, 24 Apr 2003 19:14:32 +0200
Message-ID: <ukhdavgga6o54oa5ipo0se66au9f7u4gaf@4ax.com>

On 23 Apr 2003 09:26:24 -0700, datavector_at_hotmail.com (Ken Chesak) wrote:

>I can create a sequence using sqlplus but when I put the same code in
>a stored procedure there is insufficient privileges. The same proc
>drops the sequence without an error.
>
>
>execute sp_sequence;
>sql = create sequence RIDES_DEV.xxx
> increment by 1 start with 1
> maxvalue 999999999999999999 minvalue 1 cache 20
>nocycle noorder
>BEGIN sp_sequence; END;
>
>*
>ERROR at line 1:
>ORA-01031: insufficient privileges
>ORA-06512: at "RIDES_DEV.SP_SEQUENCE", line 19
>ORA-06512: at line 1
>
>
>sp_sequence create or replace
>as
>begin
>
> v_sql := 'create sequence RIDES_DEV.xxx
> increment by 1 start with 1
> maxvalue 999999999999999999 minvalue 1 cache 20 nocycle noorder';
> dbms_output.put_line('sql = '|| v_sql );
> execute immediate v_sql;
>
>end;

This question has been asked over and over and over and over again, preferably by people, who didn't any research of their own before posting on metalink or on the newsgroups archives at http://groups.google.com

The answer is you have create sequence privilege through a role, and roles are ignored during compilation of stored procedures, as they are volatile.
Depending on version (which you too fail to mention) either grant create sequence directly (8.0 and lower) or create the procedure with authid current_user.

And PLEASSSSSSSSSSSSSSSSE ALLLLLLLLLLLLLLWAYS search the archives

BEFORE posting, to avoid getting ignored with these FAQs

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Apr 24 2003 - 12:14:32 CDT