Re: dynamic SQL with DBMS_SQL

SHO wrote:

> Hi ,
> I am attempting to use DBMS_SQL to dynamically create tables or
> views.
> These are my sample codes:
>
> PROCEDURE DYNSQL AS
> cur integer;
> rc integer;
> BEGIN
> cur := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.V7);
> rc := DBMS_SQL.EXECUTE(cur);
> DBMS_SQL.CLOSE_CURSOR(cur);
> END;
>
> It compiles fine but when I try to run it, I get the following
> errors:
>
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
> ORA-06512: at "SYS.DBMS_SQL", line 25
> ORA-06512: at "HUM_WWW00.DYNSQL", line 6
> ORA-06512: at line 4
>
> I have usd to select and update using DBMS_SQL dynamically without
> any
> problem
> I am pretty sure I have privileges create table and views. Would I
> need
> higher privs to dynamically create tables or views?
>
> Thanks in advance for your help
>
> SH

 When you try to execute an DML statement in a stored procedure, you have to be granted as a user and not to your role. Grant CREATE VIEW to your user. Received on Sun Jul 13 1997 - 00:00:00 CEST