Re: Help!! DDL in PL/SQL

Agi,

You must use an explicit grant (ex. Grant Execute) on sys.dbms_sql. This must be done by SYS. You cannot get privilege through a role like DBA.

Hope this helps.

Mike

Agi <agichen_at_my-deja.com> wrote in message news:7rkrg6$qu$1_at_nnrp1.deja.com...
> Hi,theres,
> I create a procedure like follows for executing DDL in PL/SQL,
>
> 1 CREATE OR REPLACE PROCEDURE runddl(p_sql_text varchar2) IS
> 2 l_cursor integer default 0;
> 3 rc integer default 0;
> 4 stmt varchar2(1000);
> 5 BEGIN
> 6 l_cursor:=dbms_sql.open_cursor;
> 7 dbms_sql.parse(l_cursor,p_sql_text,dbms_sql.native);
> 8 rc:=dbms_sql.execute(l_cursor);
> 9 dbms_sql.close_cursor(l_cursor);
> 10* END;
> 11 /
>
> Procedure created.
>
> SQL>
> SQL> begin
> 2 runddl('create table xxx(id number) ');
> 3 end;
> 4 /
> begin
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "AGI.RUNDDL", line 7
> ORA-06512: at line 2
>
> user Agi with DBA role,what's wrong ??
> Any hint ??
>
> Agi Chen
>
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Tue Sep 14 1999 - 20:45:15 CDT