Re: Problem with DBMS SQL in stored procedures

Jairam,

See whether you have the "CREATE TABLE" privilege through role. If yes, get this privilege directly and try it.

Hope this will work.

Bala.

Jairam, Kuppuswamy, nck_at_hotmail.com 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
>
> Jairam Kuppuswamy
> nck_at_hotmail.com
Received on Tue May 13 1997 - 00:00:00 CEST