在Oracle触发代码中使用'exec' [英] Using 'exec' in Oracle trigger code

问题描述

我试图创建一个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存储过程?

这篇关于在Oracle触发代码中使用'exec'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！