Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Dipro.

Asked: August 14, 2017 - 6:33 am UTC

Last updated: September 20, 2017 - 2:41 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,
We have a table that is populated from a UI. The requirement is that with every new addition a webservice call has to be made based on the values added to a external server.
In such scenarios our previous approach has been to write a java thread to poll the table and sleep for a time period when no more records.
The poller puts the data in an JMS queue from where the MDB picks it up and does the webservice call.
A status column is maintained in the table which determines a record's state (new , picked by poller, successful webservice call etc.) .

This of course results in CPU cycles being wasted when there is no new data.

Other possible approaches seem to be using Advanced Queues or DBMS_alerts but in all such cases triggers would be needed and that seems is discouraged.

Any suggestions regarding the best approach?

and Chris said...

Well. You don't have to use triggers to use Advanced Queuing. You can call it in your app as part of the transaction that adds the data.

If for some reasons you can't do this, you could look into Continuous Query Notification. With this your app registers a table or query with the database. Then the database will inform the app when there are changes to the table/query.

So instead of having to poll the database, it pushes changes to the app.

Read about it at:

https://docs.oracle.com/database/121/ADFNS/adfns_cqn.htm#ADFNS018

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Advantage of AQ over Weblogic Queue

A reader, September 18, 2017 - 9:43 am UTC

So will this approach(ie. using AQ from the app) have any significant advantage over the old poller-JMSqueue combination ?
In the old architecture the MDB picked the message from the JMS Queue but now it will have to connect through a foreignServer/datasource to the remote database ---will this get rid of any potential advantage of not using the poller?
Chris Saxon
September 18, 2017 - 12:53 pm UTC

"In the old architecture the MDB picked the message from the JMS Queue but now it will have to connect through a foreignServer/datasource to the remote database"

You've completely lost me there ;)

What exactly are you changing?

Clarification

A reader, September 19, 2017 - 6:09 am UTC

Old architecture:
APP-->DB-->Poller-->Weblogic JMS Queue-->MDB

New proposed architecture:
APP-->Advanced Queue in DB-->MDB

I wanted to know if the profit in getting rid of the poller will be lost because now the MDB will have to connect to the DB instead of the JMS queue in weblogic.

Chris Saxon
September 19, 2017 - 3:49 pm UTC

And what exactly is MDB? An Oracle Database?

A reader, September 20, 2017 - 6:00 am UTC

MDB : Java Message Driven Bean. It is deployed in the same weblogic where the JMS Queue is and reads from it. In the new approach it will be pointed to the AdvancedQ.
Chris Saxon
September 20, 2017 - 2:41 pm UTC

OK. I'm not familiar with Java Message Driven Beans. But typically when you connect two queues together, when you enqueue on one the other dequeues straight away. No need for you to write a poller.

So I would expect this to use less resources. But, as always, you need to test in your environment to see what really happens.

Option

Racer I., September 21, 2017 - 11:16 am UTC

Maybe try

https://slobaray.com/2015/02/05/calling-web-services-from-oracle-plsql/

directly via AQ and skip the JMS/MDB-Step.

Main problem may be rights and firewalls and such.

I guess all asynchronous processing involves some polling somewhere. The assumption is that dedicated frameworks (like MS providers) are fairly optimized by now.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library