Re: DBMS_SQL: Problem with Some DDL's (create view ....)

Adam Tadj wrote:
>
> I am trying to to execte a DDL module using SQL_DMS.PARSE, but have'nt
> had any luck yet. My intention is to use it for the creating views
> dynamically. There error says "insufficient privileges", but we tried
> it from the DBA account and got the same result. Here is the PL/SQL:
>
> PROCEDURE test_view_p IS
> cursor_handle INTEGER;
> exec_var INTEGER;
> BEGIN
> cursor_handle := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(cursor_handle,
> 'CREATE VIEW ops$a_user.a_view (a_col) AS SELECT hr '||
> 'FROM ops$a_user.a_table',
> DBMS_SQL.NATIVE);
> DBMS_SQL.CLOSE_CURSOR (cursor_handle);
> END;
>
> and here is the error messages I get when I run it from SQL>:
> SQL> EXECUTE test_view_p;
> begin test_view_p; end;
> *
> ERROR at line 1:
> ORA-01031: insufficient privilages
> ORA-06512: at "SYS.DBMS_SYS_SQL" line 239
> ORA-06512: at "SYS.DBMS_SQL", line 25
> ORA-06512: at "OPS$A_USER.TEST_VIEW_P, line 8
> ORA-06512: at line 1
>

  The procedure is executed by using the privileges of the procedure owner but not the person who runs it. As a result, you should ensure the table owner has the right before accessing ops$a_user.a_table granted to him directly.  

---
Name   : Lun Wing San
Title  : Oracle Application Developer of Hong Kong Productivity Council
         Oracle Database Administrator and System Administrator of QRC
Phone  : (852)27885841