1. Overview
This document talks about the steps to working with Oracle Advance queue. Advance queue is awesome feature of Oracle. By using this technique we can exchange the messages between two different application.The application may be in different language or platform like PLSQL,Java, .Net,Python,etc…
2. Technologies and Tools Used
The following technology has been used to achieve requirement
- Oracle PLSQL
3. Use Case
Assume that there is a requirement to achieve the customer needs by using queue features .
4. Architecture
The below Requirements needs to use the AQ technique
- Oracle database (>8)
- Packages
- Permissions
The below Packages are used to implement AQ features
DBMS_AQADM
–Creating or dropping queue tables that contain one or more queues
–Creating, dropping, and altering queues, which are stored in a queue table
–Starting and stopping queues in accepting message creation or consumption
DBMS_AQ
–Creating a message to the specified queue
–Consuming a message from the specified queue
Step 1: Create Object TYPE:
CREATE OR REPLACE TYPE PS_QT_O_QUEUE_TAB_PTP AS OBJECT (
eno NUMBER,
empname VARCHAR2 (40),
ejob Varchar2(20)
);
Step2: Create Queue Table
BEGIN
DBMS_AQADM.create_queue_table
(queue_table => ‘PS_QT_QUEUE_TAB_PTP’,
queue_payload_type => ‘PS_QT_O_QUEUE_TAB_PTP’
);
END;
Step3: Create Queue
BEGIN
DBMS_AQADM.create_queue (queue_name => ‘PS_QQ_QUEUE_TAB_PTP’,
queue_table => ‘PS_QT_QUEUE_TAB_PTP’
);
END;
Step4: Start Queue
BEGIN
DBMS_AQADM.start_queue (queue_name => ‘PS_QQ_QUEUE_TAB_PTP’);
END;
Step5: Enqueue
We send request to the application with carry some messages or request through payload that will be send to queue.So every request added in queue like list.This process is call enqueue.
DECLARE
enqueue_options DBMS_AQ.enqueue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_handle RAW(16);
message ps_qt_o_queue_tab_ptp;
BEGIN
message := ps_qt_o_queue_tab_ptp(001, ‘TEST MESSAGE’, ‘MANAGER’);
DBMS_AQ.ENQUEUE(
queue_name => ‘PS_QQ_QUEUE_TAB_PTP’,
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message,
msgid => message_handle);
COMMIT;
END;
Step6: Dequeue
After getting the request that will read and processed and it will be removed from queue. This process is called as dequeue.
CREATE OR REPLACE PROCEDURE quene_ins_ptp (
iv_eno IN NUMBER,
iv_empname IN VARCHAR2,
iv_ejob IN VARCHAR2
)
AS
BEGIN
INSERT INTO emp
(empno, ename, job
)
VALUES (iv_eno, iv_empname, iv_ejob
);
END;
/
DECLARE
reginfo SYS.aq$_reg_info;
descr SYS.aq$_descriptor;
dequeue_options DBMS_AQ.dequeue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_handle RAW (16);
MESSAGE ps_qt_o_queue_tab_ptp;
BEGIN
dequeue_options.msgid := descr.msg_id;
dequeue_options.consumer_name := descr.consumer_name;
DBMS_AQ.dequeue (queue_name => ‘PS_QQ_QUEUE_TAB_PTP’,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => MESSAGE,
msgid => message_handle
);
quene_ins_ptp (iv_eno => MESSAGE.eno,
iv_empname => MESSAGE.empname,
iv_ejob => MESSAGE.ejob
);
COMMIT;
END;
Step7: Stop and Drop Queue
BEGIN
DBMS_AQADM.STOP_QUEUE(queue_name => ‘PS_QQ_QUEUE_TAB_PTP’);
DBMS_AQADM.DROP_QUEUE(queue_name => ‘PS_QQ_QUEUE_TAB_PTP’);
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => ‘PS_QT_QUEUE_TAB_PTP);
END;
