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.]
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?