Re: DBMS_SQL

Hi!
Remember that privileges like Create Table granted through roles like RESOURCE, CONNECT are not valid inside procedures. Grant the CREATE TABLE PRIVILEGE directly to the user and then execute the DBMS_SQL and you won't get that error.

Good luck

Venkat K Reddy

Jairam, Kuppuswamy wrote:
>
> I tried executing a ddl statement using dbms_sql. this works in an anonymous
> plsql block, but while trying to do the same in a stored procedure
> i get the error of insufficient privileges.
>
> What i am trying to do is as follows
>
> SQL> declare
> 1 cid integer
> 2 begin
> 3 cid := dbms_sql.open_cursor;
> 4 dbms_sql.parse(cid,'create table jai(ram char(3))',dbms_sql.native);
> 5 dbms_sql.close_cursor(cid);
> 6 end;
> 7 /
>
> PL/SQL Procedure executed successfully.
>
> SQL> create or replace procedure proc1 as
> 1 cid integer;
> 2 begin
> 3 cid := dbms_sql.open_cursor;
> 4 dbms_sql.parse(cid,'create table jai(ram char(3))',dbms_sql.native);
> 5 dbms_sql.close_cursor(cid);
> 6 end;
> 7 /
>
> Procedure Created.
>
> SQL> execute proc1
> begin proc1; end;
>
> *
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
> ORA-06512: at "SYS.DBMS_SQL" , line 25
> ORA-06512: at "PROC1", line 5
> ORA-06512: at line 1
>
> SQL>
>
> I would appreciate if anyone could solve this problem of mine.
> I tried this on a user with DBA privileges but with the same result.
> It only works for the SYS account.
>
> Thanks in advance
> bye
Received on Wed May 14 1997 - 00:00:00 CDT