DDL in PL/SQL procedure

From: Christian Timm <timm_at_ifgi.uni-muenster.de>
Date: Wed, 03 Nov 1999 15:31:18 +0100
Message-ID: <38204736.D5D869BF@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:31:18 CST