Platform: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 (A pleasure to have moved to Oracle 19c)
Hello Experts, As always, my admiration for your time and expertise in making PL/SQL developers strive forward. We have been extensively using Oracle Queues. In many cases, Oracle itself has its Subscriber. But in one case Java has to DEQUEUE as it communicates with a Webservice that is inaccessible to Oracle. Meaning, Java (through its JDBC) calls an Oracle API where DEQUEUEs and returns a value. Now the ISSUE: Java doesn't know when to call, because it has no visibility if QUEUE has a value. So, it's constantly pinging (meaning keep calling the DEQUEUE function). To give an idea, its calling 6-7 times a sec. As PL/SQL Developers we published the API to DEQUEUE like any other GET API. But when we checked our logs and traffic we see a huge magnitude of empty calls. Now here is what we researched:
1. There was an inbuilt Weblogic feature (driver) that works with Oracle Advanced Queues. They tried that and it didn't work. The explanation was not given to us (PL/SQL Developers) as to why.
2. Even if we stack these values in a table and write a GET API, that API will be pinged constantly.
3. We (PL/SQL Developers) suggested to give us a JAVA Class so that Oracle can invoke it as and when our Subscriber picks a value. This was dismissed stating: "Lower entities like DB should never make upward calls to MW (Middleware), Only MW will call to DB to either GET or PUT as its a Security hole"
4. We asked to put a timer so that it can call once in few secs. But to avoid stainless in data, it has to immediate.
5. We asked to have a sleep mode when no data is fetched in the previous call if data comes then do consecutively.
Found this article in Oracle documentation: https://docs.oracle.com/cd/B10501_01/appdev.920/a96587/apexampl.htm#62184
Kindly advise if there is an alternative we can suggest. Because we have many queues coming up which Java has to dequeue. And we (DB) can't afford all of them doing the same thing. We feel its no different than a brute force to overwhelm DB sessions even if its a simple GET Call.
My sincere thanks. Regards Aj