Monday, February 11, 2019

How to create an Advanced oracle Queue

I was asked to create a advanced oracle queue which is used to hold data for a limited period of time with a concept of first in and first out the data is stored for short amount of time.

They asked me to create queue and table and start it. Create one if it does not exist was the instruction. We are going to create in AQ_ADMIN and table name as QUEUETABLE and queue as QUEUENAME

select OWNER, QUEUE_TABLE, OBJECT_TYPE, type from DBA_QUEUE_TABLES where QUEUE_TABLE = 'QUEUETABLE ';

#Create queue table: 

begin
DBMS_AQADM.CREATE_QUEUE_TABLE(
     queue_table        => 'AQ_ADMIN.QUEUETABLE ',
     queue_payload_type => 'SYS.AQ$_JMS_MESSAGE',
     sort_list          => 'PRIORITY,ENQ_TIME',
     storage_clause     => 'TABLESPACE QDATA',
     comment            => 'Q_EZNETSVCS_BAMS_NORMALIZED',
     multiple_consumers => FALSE
     ) ;

#Create queue: 

DBMS_AQADM.CREATE_QUEUE(
     queue_name         => 'AQ_ADMIN.QUEUENAME',
     comment            => 'YOUR_COMMENT',
     max_retries        => '6',
     retry_delay        => '6',
     retention_time     => '0',
     queue_table        => 'AQ_ADMIN.QUEUETABLE') ;

#This is to hold data:

DBMS_AQADM.START_QUEUE(
     queue_name         => 'AQ_ADMIN.QUEUENAME') ;

#This is to hold errors:

DBMS_AQADM.START_QUEUE(
     queue_name         => 'AQ_ADMIN.AQ$_Q_EZNETSVCS_BAMS_E',
     enqueue            => FALSE,
     dequeue            => TRUE) ;
end ;
/

No comments:

Post a Comment