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 ';
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 ;
/
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:
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