如此这般就可以了:
CREATE OR REPLACE PROCEDURE sp_test
AS
BEGIN
dbms_utility.exec_ddl_statement('
CREATE OR REPLACE TRIGGER system.tr_aaa_delete
AFTER DELETE ON system.aaa
FOR EACH ROW
DECLARE
str_sqltext VARCHAR2(200);
BEGIN
INSERT INTO bbb VALUES(''1'', ''ddd'');
END;'
);
END sp_test;
CREATE OR REPLACE PROCEDURE sp_test
AS
BEGIN
dbms_utility.exec_ddl_statement('
CREATE OR REPLACE TRIGGER system.tr_aaa_delete
AFTER DELETE ON system.aaa
FOR EACH ROW
DECLARE
str_sqltext VARCHAR2(200);
BEGIN
INSERT INTO bbb VALUES(''1'', ''ddd'');
END;'
);
END sp_test;
create or replace procedure sp_test
as
str varchar2(500);
BEGIN
str:='create or replace trigger system.tr_aaa_delete
after delete on system.aaa
for each row
DECLARE
str_sqltext VARCHAR2(200);
BEGIN
insert into bbb values(''1'',''ddd'');
END';
execute immediate str; --grant create any trigger to user;权限
END sp_test;
用system登陆则没有权限(好像dbms_utility是sys的)。可我必须用system登陆。
程序怎么写呢?我在begin后加入 :connect sys/change_on_install@yudb;
出错:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/2 PLS-00103: 出现符号"CONNECT"在需要下列之一时:
begindeclareexitforgoto
ifloopmodnullpragmaraisereturnselectupdatewhile
<an identifier><a double-quoted delimited-identifier>
<a bind variable><<closecurrentdeletefetchlockinsertopen
rollbacksavepointsetsqlcommit<a single-quoted SQL string>
符号"if在"CONNECT"继续之前已插入。why?
没有通过。(我用system登陆)LINE/COL ERROR
-------- -----------------------------------------------------------------
13/9 PLS-00103: 出现符号"IMMEDIATE"在需要下列之一时:
:=.(@%;why?
create or replace procedure sp_test
as
str varchar2(500);
str_compile varcha2(300);
BEGIN
str:='create or replace trigger system.tr_aaa_delete
after delete on system.aaa
for each row
DECLARE
str_sqltext VARCHAR2(200);
BEGIN
insert into bbb values(''1'',''ddd'');
END';
execute immediate str; --grant create any trigger to user;权限
str_compile := 'alter trigger system.tr_aaa_delete compile';
execute immediate str_compile ;
END sp_test;