在Oracle触发器cod中使用“exec”

Question

我试图创建一个Oracle触发器，在表被插入或更新后，触摸Linux主机中的一个文件。在

所以我创建了一个这样的shell脚本：

#!/bin/bash
touch  /export/home/oracle/shell/a.txt

并修改权限：

^{pr2}$

然后我使用sys login oracle创建一个调度程序作业，如下所示：

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触发器，并按如下方式调用此调度程序作业：

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.

我找到了很多解决办法，但都没有如何工作我能处理吗？请。在

Answer 1

问题是您在PL/SQL触发器代码中使用了exec。exec是一个SQL*Plus命令。删除exec，代码应该是好的：

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 Command Reference：

EXECUTE

EXEC[UTE] statement

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.

---

另请参见：

• How to execute an oracle stored procedure?