run a exe file in oracle trigger

Question

I am trying to run a .exe file in oracle trigger. i have oracle database trigger run after inserting on table, i need to run external program(exe) by this trigger through windows command like this:

i am trying this code but it dosen't work

create or replace TRIGGER GE_MAIN_NOTIFICATION_SEND AFTER INSERT ON TABLE
   REFERENCING OLD AS OLD NEW AS NEW 
   FOR EACH ROW 
BEGIN
   SYS.DBMS_SCHEDULER.create_program(
       program_name => 'UPLOADNC', 
       program_type => 'EXECUTABLE',
       program_action => 'C:\WINDOWS\SYSTEM32\CMD.exe /C c:\my_external_apps\app1.exe',
       enabled => TRUE);
END;

and this is the error...how to solve it

ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 5
ORA-06512: at "SYS.DBMS_SCHEDULER", line 36
ORA-06512: at "TEST.GE_MAIN_NOTIFICATION_SEND", line 2
ORA-04088: error during execution of trigger 'TEST.GE_MAIN_NOTIFICATION_SEND'

What happens if the user issues a `ROLLBACK` command after insert? Program would be executed anyway - is that desired? — Wernfried Domscheit, Aug 16 '17 at 06:17
no problem, if the user issues a `ROLLBACK` command after insert. I want so only notification when the database table record changed........ — Deepak, Aug 16 '17 at 06:35
i want only notification from mail when my databsse table record inserted or updated — Deepak, Aug 16 '17 at 06:37
But in case of ROLLBACK the database table record is not changed! — Wernfried Domscheit, Aug 16 '17 at 06:48
When database table record inserted or updated then can i do? — Deepak, Aug 16 '17 at 06:54
Put everything in a stored procedure, there you can control the transactions and execute external programs, e.g. by Java call. btw. `GE_MAIN_NOTIFICATION_SEND` sounds like sending a mail. This you could do in PL/SQL directly. — Wernfried Domscheit, Aug 16 '17 at 06:56

score 0 · Answer 1 · answered Aug 16 '17 at 06:49

0

Calls to DBMS_SCHEDULER will try to commit, so won't work from inside a trigger unless you go with autonomous transaction. It is ugly, but you can create a Java Stored Procedure that invokes the shell command / executable.

There's a full example on :

https://oracle-base.com/articles/8i/shell-commands-from-plsql

However, I'd recommend you use straight PL/SQL for your notification if possible (perhaps calling a Web API if you are doing something like sending an SMS).

answered Aug 16 '17 at 06:49

Gary Myers

34,963
3
49
74

Autonomous transaction may have a problem, because inside such autonomous transaction you don't see changed records (unless commited) of originating DML. However, I assume in a trigger this record would be the main interest. – Wernfried Domscheit Aug 16 '17 at 06:54
i can't create a java stored procedure because i am using `c#` and `oracle`........i want when my database table a new record inserted or updated then the trigger call a external `exc` file – Deepak Aug 16 '17 at 06:59
Did you go through the link posted? The java stored proc is not to rewrite your functionality that you have in C#. Your C# code stays there and is an exe. The Java SP is only to issue a shell command to execute the exe. – Arijit Kanrar Aug 16 '17 at 07:10

run a exe file in oracle trigger

1 Answers1

Linked