Dynamic SQL and create sequence

From: Yann CAUCHARD <yann.cauchard_at_spcconsultants.com>
Date: Mon, 7 Jan 2002 09:14:51 +0100
Message-ID: <a1blca$2jv6$1@news4.isdnet.net>

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 - 02:14:51 CST