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

If your procedure is owned by ops$a_user, you have to grant CREATE VIEW privilege directly!!! (not via role) to ops$a_user. Otherwise you have to:

1. grant CREATE ANY VIEW privilege directly!!! (not via role) to procedure owner;
2. grant SELECT on ops$a_user.a_table directly!!! (not via role) to procedure owner;

Solomon.Yakobson_at_entex.com

In article <5bjl45$ckc$1_at_newsin-1.starnet.net>,   vahidt_at_dbsun.vitek.com (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
>
> I would like to mention I can create an index on a table or run a
> SELECT or UPDATE commands with this built in procedure without any
> problem. This only occurs when creating a view or a table and that
> when it is executed from a procedure. If I ran the code interactively
> (declare .begin ....) it works fine. There was no Oracle
> documentation on this built in procedure and I read about it from a
> third party books.
>
> Your prompt assistance will be highly appreciated!
> Thanks in advance.
>
> Adam T.

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet