CODEWITHSUNDEEP
c#oracle-database
Deepak
Deepak

Reputation: 21

Invoke c# Windows service from Oracle AFTER INSERT Trigger

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

Any suggestion will be highly appreciated

Best Regards,

Upvotes: 0

Views: 936

Answers (2)

Dominic Cotton
Dominic Cotton

Reputation: 819

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.

Upvotes: 0

jason.kaisersmith
jason.kaisersmith

Reputation: 9650

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

Upvotes: 1

Related Questions