Using 'exec' in Oracle trigger code

Question

i tried to create a Oracle trigger, after the table is insert or update, touch a file in the Linux host.

so i create a shell script like that:

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

and modify the permission:

chmod +x test1.sh

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.

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;
/

but error:

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.

Answer 1

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;
/

---

From 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.

---

See also:

• How to execute an oracle stored procedure?