Re: stored proc to create sequence, insufficent priv

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;

You do not have privileges in the RIDES_DEV schema to create a sequence explicitly granted to your current schema. And, quite frankly, you should not be writing a stored procedure to do this. Just execute the code at the command line.

The above is overcomplication without redeeming benefit.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
(remove one 'x' from my email address to reply)