What are the real life cases for using Oracle advanced queuing?

What are the cases when Oracle advanced queuing is the preferred mechanism for implementing functional requirements? For example, money transfer from bank account A to bank account B might theoretically be considered as two different operations, and might be implemented separately - first, enqueue money transfer from account A (update), then enqueue money trasfer to account B (update). However, it's obvious it can't be done like that because those two operation should be done in one consistent operation - in transaction.

Maybe advanced queuing should be only considered when developing stored procedures/functions that perform some logic that is both done internally (by doing DML operations and calling other local stored proc/fnc) and externally (by calling some webservices). When using such calls to webservices we can't wrap it all into consistent transaction, so the only way is to use some queuing mechanism...

Any real life detailed examples would be appreciated.

What I'm curious about is not the details of the technology itself, but rather what are the real life cases of using such messaging because I haven't done that before. Like, why do I need to pass some data (message payload) around?

I am not as familiar with Oracle as I am with Postgres. Nonetheless, I will tell you where I see queuing approaches like this as ideal (as the author of pg_message_queue): allowing database transactions to have non-transactional side-effects with minimal complexity cost. A simple example is "when we commit a transaction saying we have shipped a part, let's send an email out notifying the customer."

You could do this without Oracle AQ, but Oracle AQ will likely make this simpler. The key factor is that this allows you to enqueue the message such that it is visible on commit, and have the message which will be the basis of an email once the database transaction commits and not before. If you try to send the message from inside the transaction, you get a number of nasty failure cases:

• If the message fails to send should we abort the transaction?

• If the message sends and the transaction later is rolled back, how do we un-send the email? We can't.

Another example may be loosely coupled applications, integrated over a message queue. Each application can largely run unaware of the other, but when transactions are committed, messages are sent to the other. If they can't be delivered immediately, no problem. We will process them when we can.

So the basic thing is that there are many cases where you want to centralize logic around the database where you can't really do everything properly in a single transaction. Being able to send messages to other components on db commit is really helpful.

And sure you could build everything yourself without AQ. But this has it already built for you.

Edit: I have re-read the Oracle docs here and they are hopelessly confusing, so I don't blame you for your confusion here. If I wasn't already somewhat familiar with the sorts of things they were talking about, I am not sure I would have been able to follow them. I am now 100% sure that my answer is on-target though.

StackExchange Database Administrators Q#25161, answer score: 7