Re: dynamic SQL with DBMS_SQL
Date: 1997/07/14
Message-ID: <5qe14r$v6d_at_dino.active.ch>#1/1
[Quoted] Hi SH
I think the problem here is, that you must grant the priviledge
'create table' direct to the user, and not via a role (e.g. connect,
resource, dba, etc.).
Example: grant create table to oracle_user_sh;
Hope this will help.
Marc
SHO <hos000_at_uleth.ca> 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
Received on Mon Jul 14 1997 - 00:00:00 CEST