Oracle AQ Creation and Configuration

To start using Oracle Advanced Queuing (AQ) first it needs to be created and configured in the database.

So, first login using an account with admin privileges :
E.g.

connect SYS/1234 as SYSDBA;
create user SAMPLE_USER identified by oracle;
grant create session, resource, AQ_ADMINISTRATOR_ROLE,AQ_USER_ROLE to SAMPLE_USER;
grant execute on DBMS_AQADM to SAMPLE_USER;
grant execute on DBMS_AQ to SAMPLE_USER;

Now login using the newly created user (SAMPLE_USER in this case) and follow the steps :

1. Create a type
   

   create or replace type FMWuser_type as object(
         empid number,
         name varchar2(60),
         email varchar2(80)
   );

2. Create the Queue Table
   

   BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(
        Queue_table        => '"SAMPLE_USER"."FMWUSER_QUEUE_TABLE"',
        Queue_payload_type => 'SAMPLE_USER.FMWUSER_TYPE');
     END;

3. Create the Queue
   

   BEGIN
   DBMS_AQADM.CREATE_QUEUE (
             queue_name =>'SAMPLE_USER.FMWUSER_queue',              
             queue_table=>'SAMPLE_USER.FMWUSER_QUEUE_TABLE');
             END;
             

4. Start the Queue
   

   BEGIN
   DBMS_AQADM.START_QUEUE('SAMPLE_USER.FMWUSER_queue');
   END;

5. Enqueue a sample data to check

   DECLARE
     enqueue_options    dbms_aq.enqueue_options_t;
     message_properties       dbms_aq.message_properties_t;
     message_handle     RAW(16);
     message           FMWUSER_TYPE ;
     message_id         NUMBER;
   BEGIN
    
    message := FMWUSER_TYPE (1234, 'Shoumya Kanti Das', 'abc@xyz.com');
    enqueue_options.VISIBILITY := DBMS_AQ.ON_COMMIT;
     enqueue_options.SEQUENCE_DEVIATION := null;
     message_properties.EXPIRATION := DBMS_AQ.NEVER;
    
       DBMS_AQ.ENQUEUE (
       queue_name => 'FMWUSER_queue',
       enqueue_options => enqueue_options,
       message_properties => message_properties,
       payload => message,
       msgid => message_handle);
       
     COMMIT;
   END;
   

6. Check if the data has been entered successfully

   SELECT user_data FROM AQ$FMWUSER_QUEUE_TABLE;
   

If everything went OK then you will be able to see the data entered in the previous step.

Hope it helps!