Re: Why the DBMS_SQL can't parse the command(trigger)?

A copy of this was sent to "wls" <cuba_at_ms.chttl.com.tw> (if that email address didn't require changing) On Tue, 14 Dec 1999 09:22:41 +0800, you wrote:

>--------------------------------------------------------------
>Why the DBMS_SQL can't parse the command?
>
>ORA-01031:insufficient privileges
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 487
>ORA-06512: at "SYS.DBMS_SQL", line 32
>--------------------------------------------------------------
>

It parsed it -- you just aren't allowed to execute such a command in a procedure. To see why, please see:

http://osi.oracle.com/~tkyte/Misc/RolesAndProcedures.html

>Procedure TEST
>IS
> iCursorID integer;
> vCommand varchar2(4096);
> iReturned integer;
> enter varchar2(2):=chr(10);
>BEGIN
>vCommand:='Create or replace trigger worker.test after insert on
>worker.uidpwd for each
>row'||enter||'declare'||enter||'begin'||enter||'null;'||enter||'end;'||enter
>||'/';
> iCursorID:=DBMS_SQL.Open_Cursor;
> DBMS_SQL.Parse(iCursorID,vCommand,DBMS_SQL.native);
> iReturned:=DBMS_SQL.Execute(iCursorID);
> DBMS_SQL.Close_Cursor(iCursorID);
>END;
>
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Dec 14 1999 - 07:07:06 CST