Create and drop Oracle DB Queue/ Queue Table

Follow the below steps to Create Queue :-

1. Create Queue Table :-

BEGIN
   SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
  (
    QUEUE_TABLE           =>        'XX.XX_CUSTOM_QT'
   ,QUEUE_PAYLOAD_TYPE    =>        'APPS_NE.XX_CUSTOM_OBJ'
   ,COMPATIBLE            =>        '10.0.0'
   ,STORAGE_CLAUSE        =>        'TABLESPACE OF YOUR ORG IF CUSTOM'
   ,SORT_LIST             =>        'ENQ_TIME'
   ,MULTIPLE_CONSUMERS    =>         FALSE
   ,MESSAGE_GROUPING      =>         0
   ,SECURE                =>         FALSE
   );

  DBMS_OUTPUT.PUT_LINE('Created queue table - ok.');
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('  Created queue table - '||SUBSTR(SQLERRM, 1, 210));
END;
/

2. Create Queue :- 

BEGIN
  SYS.dbms_aqadm.create_queue(queue_name   => 'XX.XX_CUSTOM_Q',
                          queue_table     => 'XX.XX_CUSTOM_QT',
                          queue_type      => dbms_aqadm.normal_queue,
                          max_retries     => 5,
                          retry_delay     => 1000,
                          retention_time  => 86400
                          );

  DBMS_OUTPUT.PUT_LINE('Created queue - ok.');
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Create queue - '||SUBSTR(SQLERRM, 1, 210));
END;
/

3. Start Queue :- 

BEGIN
  SYS.DBMS_AQADM.START_QUEUE
  (QUEUE_NAME => 'XXG.XX_CUSTOM_Q'
   ,ENQUEUE => TRUE
   ,DEQUEUE => TRUE
   );
END;
/

Grant Privilege to Other Schema :-

Grant Enqueue and Dequeue to other schema :- 

BEGIN
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
   privilege     =>     'ENQUEUE',
   queue_name    =>     'XXG.XX_CUSTOM_Q',
   grantee       =>     'SCHEMA_NAME',
   grant_option  =>      TRUE);
END;
/

BEGIN
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE (
   privilege     =>     'DEQUEUE',
   queue_name    =>     'XXG.XX_CUSTOM_Q',
   grantee       =>     'SCHEMA_NAME',
   grant_option  =>      TRUE);
END;
/

Drop Queue:- 

1. Stop Queue :-

BEGIN
  SYS.DBMS_AQADM.STOP_QUEUE ( QUEUE_NAME => 'XX.XX_CUSTOM_Q');
  SYS.DBMS_AQADM.DROP_QUEUE ( QUEUE_NAME => 'XX.XX_CUSTOM_QT');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

2. Drop Queue Table :- 

BEGIN
  SYS.DBMS_AQADM.DROP_QUEUE_TABLE
    (QUEUE_TABLE => 'XXG.XX_CUSTOM_QT');
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/