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

Do you have create table privileges ?

Henrik Bachmann wrote:

> 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 - 02:16:55 CST