Dynamic SQL (DDL) - ORA-01031: insufficient privileges

From: Henrik Bachmann <Henrik.Bachmann_at_bim-consulting.de>
Date: Wed, 19 Jan 2000 07:18:52 GMT
Message-ID: <38856537.81724403@news.topnet.de>

Hi there!

Is anybody out there who can give me a hint?

Consider the following procedure [Source: Oracle-Doc]:

CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS

    cursor_name INTEGER;
    ret INTEGER;
BEGIN
   cursor_name := DBMS_SQL.OPEN_CURSOR;
--DDL statements are executed by the parse call, which
--performs the implied commit

   DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.NATIVE);    ret := DBMS_SQL.EXECUTE(cursor_name);    DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
    WHEN OTHERS THEN

        DECLARE
            x_error_msg     VARCHAR2(1000)  := SQLERRM;
        BEGIN
            dbms_output.put_line(SUBSTR(x_error_msg, 1, 200));
        END;

END; The error "ORA-01031: insufficient privileges" occurs after calling the next statement (user with dba role): exec('create table acct(c1 integer)');

After creating the table acct manually there is no error calling a statement like this:

exec('drop table acct');

Why? Any ideas?

Best regards

Henrik Bachmann
mailto:Henrik.Bachmann_at_bim-consulting.de

B.I.M.-Consulting Magdeburg        http://www.bim-consulting.de/

D-39124 Magdeburg                  

Received on Wed Jan 19 2000 - 01:18:52 CST