问题描述
我试图创建一个Oracle触发器,在表插入或更新后,触摸Linux主机中的文件.
i tried to create a Oracle trigger, after the table is insert or update, touch a file in the Linux host.
所以我创建了这样的shell脚本:
so i create a shell script like that:
#!/bin/bash
touch /export/home/oracle/shell/a.txt
并修改权限:
chmod +x test1.sh
然后我使用sys登录oracle并创建一个调度程序作业,如下所示:
then i use the sys login oracle and create a scheduler job like this:
SQL> exec DBMS_SCHEDULER.CREATE_JOB(job_name=>'test1',job_type=>'EXECUTABLE',job_action=>'/export/home/oracle/shell/test1.sh');
PL/SQL procedure successfully completed.
现在,我想创建一个Oracle触发器并按以下方式调用此调度程序作业:
now i want to create a Oracle trigger and call this scheduler job like this:
CREATE OR REPLACE TRIGGER MY_OAM_LOG
AFTER INSERT OR UPDATE ON OCS_CHARGE_OFF_AUTOMATION
FOR EACH ROW
BEGIN
exec DBMS_SCHEDULER.RUN_JOB(job_name=>'test1');
END;
/
但有错误:
SQL> CREATE OR REPLACE TRIGGER MY_OAM_LOG
2 AFTER INSERT OR UPDATE ON OCS_CHARGE_OFF_AUTOMATION
3 FOR EACH ROW
4 BEGIN
5 exec DBMS_SCHEDULER.RUN_JOB(job_name=>'test1');
6 END;
7 /
Warning: Trigger created with compilation errors.
SQL> show error
Errors for TRIGGER MY_OAM_LOG:
LINE/COL
--------------------------------------------------------------------------------
ERROR
--------------------------------------------------------------------------------
2/7
PLS-00103: Encountered the symbol "DBMS_SCHEDULER" when expecting one of the fol
lowing:
:= . ( @ % ;
The symbol ":=" was substituted for "DBMS_SCHEDULER" to continue.
我搜索了很多解决方案,但都没有用.请问该如何处理?
i have search many solution,bu none of them are work.how do i deal with it?plz.
推荐答案
问题是您在触发代码PL/SQL中使用了exec
. exec
是SQL * Plus命令.删除exec
,您的代码应该不错:
The issue is that you are using exec
inside the the trigger code which is PL/SQL. exec
is an SQL*Plus command. Remove exec
and your code should be good:
CREATE OR REPLACE TRIGGER MY_OAM_LOG
AFTER INSERT OR UPDATE ON OCS_CHARGE_OFF_AUTOMATION
FOR EACH ROW
BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name=>'test1');
END;
/
来自 PL/SQL命令参考:
EXEC [UTE]语句
EXEC[UTE] statement
执行一个PL/SQL语句. EXECUTE命令通常是 当您想执行一个引用了以下内容的PL/SQL语句时很有用 存储过程.
Executes a single PL/SQL statement. The EXECUTE command is often useful when you want to execute a PL/SQL statement that references a stored procedure.
另请参阅:
See also:
这篇关于在Oracle触发代码中使用'exec'的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!