I have a Linux CentOS server hosting an Oracle 11.2 database, which communicates with an application server, which communicates with a client (a Flex application). I'll try to describe the pseudo-code I want to implement as a process as follows:
The application server has a Java program that acts like a database front end for clients. Basically, a client wishes to do some number crunching, submits some data to the Java tier, and the Java tier saves this data to the database, along with a flag used to instruct a C program that data is waiting.
The application server also has this C program on it. The C program polls the database for this flag every 1 second or so, and when the C program detects this flag to be set, the C program downloads the data using OCILIB (e.g. OCI) from the database, crunches the math, and saves a result back into the database. The C program then sets a different flag to instruct the Java tier this processing is done.
Another program in the Java tier is watching this flag that the C program will set. When it detects the flag is set, the Java tier sends a message to the client telling the client that the data is available in the database.
The client then downloads the data from the database by sending an appropriate request to the first Java program discussed above.
[A simpler approach may be to have the java tier call the C program directly (to avoid all of this polling the database, etc.), but the required JNI programming along with java queuing and multi-threading is beyond my capability as a programmer.]
QUESTIONS:
Is having 2 processes poll the database fairly common in the industry (and nothing to worry about), or is it a bad practice?
Is there a way, for example (in SQL or PL/SQL), to avoid polling by having the database call a function (e.g. C or Java) when the contents of a cell change to become a certain value (e.g. 0 = wait, 1 = call the function)?
Is the simplistic approach above reasonable or flawed somehow? Is there a better, although still straightforward, way of achieving the same result? Anything to watch out for?
Best Answer
If you must poll, see this article by a former colleague of mine.
He is particularly interested in capturing a stream of changes, but your use case sounds like you want to do a form of cross-language IPC via the database. I personally do not like polling; it's inelegant. There are a few approaches for this:
AQ. I link to the 9i docs so you can see that this is a well-established feature of Oracle. This is my own favourite technique for this kind of application. One interesting thing about AQ is that you might not even need to tell your app to wake up and fetch the data; you could send the data in the payload!
DCN. This is very straightforward to use from OCI, but I have never tried it with Java so can't comment.
Direct calls from PL/SQL into C using EXTPROC. From C you can call any other language, including Java. From that link there is some sample code on Github for doing this; it's actually quite straightforward.
If you want to get really exotic, you could do this with Oracle Coherence - I do this via C in my example, there is also a Java binding.