dynamically generation of sequences

I have nearly the necessary task to do to create sequences dynamically and I
tried to a PL/SQL procedure with the similar content below. The dbms_sql.execute
throws a exception with insufficient privileges and refuse the execution. Is
there a 'global privilege' to create sequences and if yes how do I permit this
privilege to the PL/SQL procedure?
By the way the 'funny' thing is that I can drop sequences dynamically via PL/SQL
procedure without any trouble.

any hint is appreciated,
Gerald Forthuber

peacocda_at_wwwinc.com wrote:

> You can set it with the START WITH clause of the CREATE SEQUENCE
command.
> What you will probably need to do is used the DBMS_SQL package to
dynamically
> create the CREATE SEQUENCE statement. For instance:
>
> BEGIN
> SELECT SOME_COLUMNE INTO V_SEQVAL
> FROM SOME_TABLE
> WHERE <SOME_CONDITION>; // This is your select statement you
referenced
>
> V_SQLTXT := 'CREATE SEQUENCE MY_SEQUENCE START WITH
'||TO_CHAR(V_SEQVAL);
> V_CURSOR_HANDLE := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(V_CURSOR_HANDLE,V_SQLTXT,dbms_sql.v7);
> DBMS_SQL.EXECUTE(V_CURSOR_HANDLE);
> DMBS_SQL.CLOSE_CURSOR(V_CURSOR_HANDLE);
> END;

Received on Thu Dec 17 1998 - 06:17:45 CST