Re: problem with dynamic DDL using Dbms_SQL

"PGY" <pgy_at_magma.ca> wrote in message news:JvaI9.1925$yq.59487_at_news...
> I have been struggling with the creation of some dynamic routines for use
> management in my 8.1.6 database. I coded up the logic DIRECTLY FROM THE
> HELP PAGES. It fails too. Any ideas?
>
>
> CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2) AS
> cursor_name INTEGER;
> ret INTEGER;
> BEGIN
> cursor_name := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.native);
> ret := DBMS_SQL.EXECUTE(cursor_name);
> DBMS_SQL.CLOSE_CURSOR(cursor_name);
> END;
> /
>
> BEGIN exec('CREATE TABLE zpoo (col1 NUMBER)'); END;
> /
>
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 782
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "CEAG.EXEC", line 6
> ORA-06512: at line 1
>

Almost certainly, the problem is that you do not have rights to the SYS.DBMS_SQL package. Hence the 'insufficient privileges' error message.

Be aware, as has been mentioned on this group many, many, many times that if I grant you the DBA role (as a for example) then you will be able to execute the DBMS_SQL package from a SQL*Plus session. But any PL/SQL procedures you then write calling that package will fail.

Because: PL/SQL requires that you obtain your privileges by a *direct grant* of the privileges on the underlying object, not via a role.

Hence: grant dba to fred;

Fred won't be able to execute the procedure you've written above.

Grant execute on dbms_sql, fred *will* be able to execute the exact same procedure.

Regards
HJR Received on Sat Dec 07 2002 - 01:08:54 CST