0

On an insert event in a table, i need to fetch some data in a file using C++ API and send that file to client.

So currently my plan is to Check the " After insert" event using a sql trigger and call the C++ exe from the trigger.

I found in many places that its not advisable to call an exe from a trigger. But i believe in my case it should not be a big issue as my exe is not going to update anything, rather it will just fetch some data and generate a pipe delimited file having those data.Please let me know if this has any limitations.

Question:

  1. What are the steps i should follow to call an exe from a trigger?
  2. If i call my exe from the trigger, should it cause any types of issues in database like database hang?

Note: A better approach comes to my mind is: We have our own C++ APIs using which i can connect to Database.So i can put a logic in C++ which should have a daemon logic to check the table every time and generate the file once an insertion happens in table.But the problem here is , my client don't want to have a daemon process which needs constant monitoring and increase the maintenance work.They are suggesting to go with an approach where it should run the application only when the insertion event happens.

Please help me on this whether i should go for trigger approach to call the exe from there. Also please let me know for any better approach.

user1706047
  • 369
  • 3
  • 7
  • 19

1 Answers1

0

I think a better approach would be to use a DBMS_SCHEDULER call in the trigger to create or schedule a job that will invoke your external application. This way you will decouple your database operation from the external call and yet you will be able to trigger the program when necessary instead of polling the table.

mustaccio
  • 18,234
  • 16
  • 48
  • 57
  • Thanks for the suggestion.So from trigger i should use a DBMS_SCHEDULER which in turn will call my external application.I am very new to DBMS_SCHEDULER.Just wanted to know how can i schedule the job using DBMS_SCHEDULER.i tried to follow the link [http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm#CIHHBGGI] but it just says the parameter as **Job**.So how my application will be registered with this Job.Can you please share any link where i can find this detail.Thanks In Advance. – user1706047 Sep 08 '13 at 06:32
  • Seriously? You're writing C++ code and don't know how to use google? https://forums.oracle.com/thread/557102?start=0&tstart=0 – mustaccio Sep 08 '13 at 14:16