DBMS_SQL insufficient privileges

I tried to create a Stored Procedure for DDL using DBMS_SQL. Actually I copied it from the Oracle documentation. The procedure is:

CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS cursor_name INTEGER;
ret INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.V7); ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END; I created it in the schema T00
Now connecting with user T00 the following PL-SQL code:

begin
exec ('create table a (b varchar2(10))'); end;

returns the following error:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "T00.EXEC", line 8
ORA-06512: at line 2

But the repeating the code of the SP in a PL-SQL block all Works well:

declare
cursor_name INTEGER;
ret INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cursor_name, 'create table a (b varchar2(10))', DBMS_SQL.V7);
ret := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END; Since I know that though a SP you can’t use privileges granted via roles I suppose
I need some privileges in SYS schema.

Thanks in advance for your help.

MM. Received on Tue Oct 26 1999 - 02:32:24 CDT