What are the real life cases for using Oracle advanced queuing?
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.