Invoke c# Windows service from Oracle AFTER INSERT Trigger

Question

How can we invoke c# windows service (or) dll file from Oracle AFTER INSERT Trigger

Any suggestion will be highly appreciated

Best Regards,

What have you tried? Please ask a specific question and provide code samples. Here's a similar question: https://stackoverflow.com/questions/14918695/invoke-windows-service-from-oracle-after-insert-trigger — Max Szczurek, Aug 08 '17 at 05:14

score 1 · Answer 1 · answered Aug 08 '17 at 05:14

1

As a rule, a windows service is not designed to be invoked upon a specific request, it should be running continuously in the background.

In order to get Oracle DB to do something then you will first need a trigger on your table which monitors for inserts.

Then, this should called a java stored procedure. This stored procedure can then send a message on a message bus which can be received by your service and processed.

See:
Sending a JMS Message from Oracle Database on DML Event

Oracle: Java stored procedure sending JMS Message

https://docs.oracle.com/cd/B19306_01/server.102/b14257/jm_point.htm

Another method is to get the java stored procedure to call an external program

answered Aug 08 '17 at 05:14

jason.kaisersmith

8,712
3
29
51

i need when my oracle database table is changed then the trigger should fired a mail from c# window service – Deepak Aug 08 '17 at 05:24
That's not how a trigger works. A trigger is fired when something happens on a database table, it only exists in the database. Use the procedure I suggest above to get your windows service to do whatever you need to do, which might be to send a message/event to another program. – jason.kaisersmith Aug 08 '17 at 05:26
how to invoke a c# window service in oracle trigger when my database table a row inserted or update – Deepak Aug 08 '17 at 05:27
yes that's the point when my database table record changed or inserted then a trigger is fired a mail from c# window service – Deepak Aug 08 '17 at 05:31
is it possible? – Deepak Aug 08 '17 at 05:32
No. A trigger exists only IN the oracle database. If you want to send an email then there are other ways, such as this: http://www.orafaq.com/wiki/Send_mail_from_PL/SQL – jason.kaisersmith Aug 08 '17 at 05:39

score 0 · Answer 2 · answered Aug 09 '17 at 14:59

0

I have solved this issue by making the Oracle trigger call a WebApi service using utl_http. This way the "service" can sit waiting for requests and the trigger is light touch. Here is a sample of my trigger:

declare 
  content varchar2(4000);
  req utl_http.req;
  res utl_http.resp;
  url varchar2(4000) := 'http://server.com/api/Function';
begin

  content := '{ "Message": "Hello" }';
  req := utl_http.begin_request(url, 'POST',' HTTP/1.1');
  utl_http.set_header(req, 'user-agent', 'mozilla/4.0'); 
  utl_http.set_header(req, 'content-type', 'application/json'); 
  utl_http.set_header(req, 'Content-Length', length(content));

  utl_http.write_text(req, content);
  res := utl_http.get_response(req);
  if res.status_code <> 200 then
    dbms_output.put_line(res.reason_phrase);
  end if;
  utl_http.end_response(res);
end;

You can put whatever logic you want into the WebApi endpoint. If you really want a windows service, you could have the trigger write to something like RabbitMQ and have the service consume the exchange queue.

answered Aug 09 '17 at 14:59

Dominic Cotton

789
10
34

ok but how to create a oracle trigger for invoke c# window servie? that automatically fired mail when my database record change or a new row inserted in the table...................... – Deepak Aug 09 '17 at 17:33
The code above will fire when a change or insert happens (as long as its created as a trigger), and it will send a message to the URL listed - this service could easily send a mail you just need to fill the content with whatever you want to send, and enable the endpoint. – Dominic Cotton Aug 10 '17 at 09:04
@ Dominic......the above code you have provided me that code how to use in a oracle trigger and service – Deepak Aug 10 '17 at 09:46
Not sure I understand your comment, I explained in answer to your original question for a suggestion - mine is to call a WebApi service from the trigger using the code above - I wasn't sure I needed to post an example of a WebApi endpoint? Good luck with your solution – Dominic Cotton Aug 10 '17 at 09:57
CAN I USE HERE MY WEBSERVICE URL `url varchar2(4000) := 'http://server.com/api/Function';` – Deepak Aug 10 '17 at 10:10

Invoke c# Windows service from Oracle AFTER INSERT Trigger

2 Answers2