Wednesday, May 11, 2016

SOA AqAdapter Not Dequeuing Messages

Messages were not being dequeued by the SOA AqAdapter in a composite. The error in the logs indicated a misleading reason for the behavior - AQ_INVALID_QUEUE_TYPE.

The queue object and queue existed and could be queried in the database directly. Messages could be enqueue/dequeued using anonymous blocks with no problem. However, the AqAdapter would not dequeue messages as expected.

ERROR

Please correct the reported issue and redeploy the BPEL process.

: Endpoint Activation Error.

AdapterFrameworkImpl::endpointActivation - Endpoint Activation Error.

The Resource Adapter AQ Adapter was unable to activate the endpoint oracle.tip.adapter.aq.inbound.AQDequeueActivationSpec:{QueueName=myqueue, DatabaseSchema=myuser, SchemaValidation=false} due to the following reason: BINDING.JCA-11976

AQ_INVALID_QUEUE_TYPE.

Unable to obtain the correct queue type.
Queue does not exist or not defined correctly.
Drop and re-create queue.

Please correct the reported issue and redeploy the BPEL process. […]


ANALYSIS

Similar to the DbAdapter distributed polling, the AqAdapter queries the query queue table using SELECT FOR UPDATE SKIP LOCKED.

You can view the actual SQL query by finding the sql_id of the DML that was passed to the database.

select sql_id,prev_sql_id,a.*
from gv$session a
where machine = ‘hostname';

select *
from gv$sql
where sql_id = sql_id;


FIX

In addition to granting execute to the object used in the queue and using DBMS_AQADM to grant privileges to the queue itself,

grant execute on queue_object to role;
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
   privilege      => 'DEQUEUE',
   queue_name     => 'myqueue',
   grantee        => 'myuser',
   grant_option   => FALSE);

the jdbc user that the AqAdapter employs to connect to the database also needs select and update to the queue table. This table is what the AqAdapter polls for new messages in the queue.

grant select on aq$myqueue_query_table to role;grant update on aq$myqueue_query_table to role;