Re: Dynamic SQL and create sequence

I found the answer :

the PL/SQL is ran with 'no' user, it means no privilege to create objects.

The only way i found (i use) is to grant create sequence directly to the user (not through a role), and It works fine.

Yann

Yann CAUCHARD a écrit dans le message ...
>Hi !
>
>I work with Oracle 8.1.6 and I try to create sequences with dynamic SQL.
>
>I created a package TEST owned by user OPS$PENLIMS :
>
>CREATE OR REPLACE PACKAGE body test as
>procedure test is
> cid number(10);
>begin
> cid := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cid, 'CREATE SEQUENCE OPS$PENLIMS.TOTO ',
>dbms_sql.native);
> DBMS_SQL.CLOSE_CURSOR(cid);
>end;
>end;
>
>When I run It, I got these messages :
>
>SQL> begin ops$penlims.test.test; end;
> 2 /
>begin ops$penlims.test.test; end;
>*
>ERREUR à la ligne 1:
>ORA-01031: privilèges insuffisants (insufficient privileges)
>ORA-06512: à "SYS.DBMS_SYS_SQL", ligne 782
>ORA-06512: à "SYS.DBMS_SQL", ligne 32
>ORA-06512: à "OPS$PENLIMS.TEST", ligne 13
>ORA-06512: à ligne 1
>
>I connected SYS and granted EXECUTE on DBMS_SQL and DBMS_SYS_SQL to
>OPS$PENLIMS.
>
>Still doesn't work.
>
>What do I forgot ????
>
>(Of course It works when connected OPS$PENLIMS in SQL*PLUS).
>
>Thanks.
>
>
Received on Mon Jan 07 2002 - 07:12:20 CST