Re: DDL in PL/SQL procedure

You just don't have the "create table" privilege by yourself maybe via a role but roles are not enable during the execution of the procedure.

--
Have a nice day
Michel

Christian Timm <timm_at_ifgi.uni-muenster.de> a écrit dans le message : 38204736.D5D869BF_at_ifgi.uni-muenster.de...
>
> We are trying to use a PL/SQL procedure to create several tables.
> But, whatever we do, we got the error:
>
> ORA-01031: insufficient privileges
>
> We use Oracle 8.0.4 on NT.
> The specific user has granted the connect and resource role. The table
> should be created in the schema of the specific user. (BTW: It also
> doesn't work with the System user)
>
> The code of the procedure is ok. I think there is not really a problem
> with the user rights in the database, but what is it?
>
> Here is a short sample:
>
> SQLWKS> CREATE OR REPLACE PROCEDURE DYNSQL AS
> 2> cur integer;
> 3> rc integer;
> 4> BEGIN
> 5> cur := DBMS_SQL.OPEN_CURSOR;
> 6> DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)',
> DBMS_SQL.NATIVE);
> 7> rc := DBMS_SQL.EXECUTE(cur);
> 8> DBMS_SQL.CLOSE_CURSOR(cur);
> 9> END;
> 10> /
> Statement processed.
> SQLWKS> execute dynsql
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "ATKIS_OK.DYNSQL", line 6
> ORA-06512: at line 2
>
>
> Thanks in advance,
>
> Christian
>
>
>
>
> --
> Christian Timm Tel. +49 (0) 251 83 31956
> Institute for Geoinformatics (IfGI) Fax +49 (0) 251 83 39763
> University of Muenster Email timm_at_ifgi.uni-muenster.de
>
> Robert-Koch-Str. 26-28 IfGI
> http://ifgi.uni-muenster.de
> D - 48149 Muenster (Germany) GIO
> http://gio.uni-muenster.de
>
>
Received on Wed Nov 03 1999 - 08:44:11 CST