Re: Why the DBMS_SQL can't parse the command(trigger)?
Date: 14 Dec 1999 05:09:05 GMT
Message-ID: <19991214000905.15415.00000381_at_ng-cv1.aol.com>
>
>
>--------------------------------------------------------------
>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
>--------------------------------------------------------------
>
>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;
I have not tried building a trigger, but I have done a package that way... you
should be able to do it, but you need to put a BEGIN and END around the trigger
definition, and you do not need the trailing '/' (You do not need the "enter"
either) Try
Procedure TEST
IS
iCursorID integer;
vCommand varchar2(4096);
iReturned integer;
enter varchar2(2):=chr(10);
BEGIN
vCommand:=
'Begin; Create or replace trigger worker.test after insert on worker.uidpwd for
each
row declare begin null;end;end'
iCursorID:=DBMS_SQL.Open_Cursor;
DBMS_SQL.Parse(iCursorID,vCommand,DBMS_SQL.native);
iReturned:=DBMS_SQL.Execute(iCursorID);
DBMS_SQL.Close_Cursor(iCursorID);
END;
Do you have CREATE TRIGGER priv?
Received on Tue Dec 14 1999 - 06:09:05 CET