My Profile
Active Members
TodayLast 7 Days
more...
Awards & Gifts
Online Exams
Fresher Jobs
Our fresher job section is exclusively for fresh graduates! Find jobs for freshers in major Indian
cities including Bangalore, Chennai, Hyderabad, Pune or Kochi
Resources
Find educational articles, blogs, discussion threads and other resources.
Colleges
Find details about any college in India or search for courses.
|
Oracle Advanced Queuing-Code Snippet
Posted Date: 17 Apr 2008 Resource Type: Articles/Knowledge Sharing Category: Computer & Technology
|
Posted By: Vinothkumar K Member Level: Gold
Rating:  Points: 5
|
|
|
Oracle AQ is first introduced from Oracle 8 which is used to migrate the data from one database to another using queue mechanism.
Two basic Operations in Oracle AQ
Enqueue
Dequeue
Basic Objects Needed
User defined Object Type
Queue table
Queue
User defined Object Type
CREATE OR REPLACETYPE TYP_QUE_REC AS OBJECT
(FIELD1 NUMBER,
FIELD2 VARCHAR2 (100),
FIELD3 DATE)
Queue table
BEGIN
sys.dbms_aqadm.create_queue_table
(queue_table => ,
queue_payload_type => ,
sort_list => 'ENQ_TIME' ,
COMMENT => ,
multiple_consumers => TRUE ,
message_grouping => DBMS_AQADM.NONE ,
storage_clause => ,
compatible => ,
primary_instance => '0' ,
secondary_instance => '0');
COMMIT;
END;
Queue Creation
BEGIN
sys.dbms_aqadm.create_queue
(queue_name => ,
queue_table => ,
queue_type => sys.dbms_aqadm.NORMAL_QUEUE ,
max_retries => '5' , retry_delay => '0' ,
retention_time => '0' ,
COMMENT => '');
END;
Start the Queue
BEGIN
sys.dbms_aqadm.start_queue( queue_name => ,
enqueue => TRUE ,
dequeue => TRUE);
END;
Enqueue Snippet
DECLARE
queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
message_id RAW(16);
my_message aqadm.queue_message_type;BEGIN
my_message := aqadm.queue_message_type(
1,
'This is a sample message',
'This message has been posted on ' ||
TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS'));DBMS_AQ.ENQUEUE(
queue_name =>
enqueue_options => queue_options,
message_properties => message_properties,
COMMIT;
END;
Dequeue Snippet
DECLARE
queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
message_id RAW(2000);
my_message aqadm.queue_message_type;
BEGIN
DBMS_AQ.DEQUEUE(
queue_name =>
dequeue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id );
COMMIT;
DBMS_OUTPUT.PUT_LINE(
'Dequeued no: ' || my_message.no);
DBMS_OUTPUT.PUT_LINE(
'Dequeued title: ' || my_message.title);
DBMS_OUTPUT.PUT_LINE(
'Dequeued text: ' || my_message.text);
END;
|
Responses
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|
Watch TV Channels
Watch Asianet TV onlineKairali TV in InternetSurya TV onlineAmritha TV Channel
|