CODEWITHSUNDEEP
sqloracle-databaseasynchronousplsqloracle12c
tonyf
tonyf

Reputation: 35539

Best means to poll an Oracle table based on an asynchronous response

How can I handle an asynchronous response that eventually updates a status flag in an Oracle table?

I basically have a PL/SQL routine that make a REST call using APEX_WEB_SERVICE API.

My question is, asynchronously, this will eventually update a status flag within a table, which will tell me whether the operation was OK or FAIL.

What is the best way to poll this table to check if a response of OK or FAIL has been returned using Oracle PL/SQL?

I was looking at DBMS_LOCK.sleep() but unsure if this is the best approach. Could DBMS_ALERT also work for this?

Upvotes: 0

Views: 343

Answers (1)

pmdba
pmdba

Reputation: 7033

Rather than poll the table at an interval, I would recommend using Oracle Advanced Queues along with Oracle Scheduler. AQ is designed exactly for this sort of thing. You can create a "scheduled" job that is triggered by a message (sent by the asynchronous process at the same time it updates the table) being sent to the queue. Scheduler sees the message and runs the appropriate job or job chain to finish the processing.

See here for a basic example: https://pmdba.wordpress.com/2017/08/21/aq-basics/

Upvotes: 1

Related Questions