{"id":1487,"date":"2010-08-27T14:09:06","date_gmt":"2010-08-27T19:09:06","guid":{"rendered":"http:\/\/jianmingli.com\/wp\/?p=1487"},"modified":"2011-02-14T20:50:10","modified_gmt":"2011-02-15T01:50:10","slug":"oracle-aq","status":"publish","type":"post","link":"https:\/\/jianmingli.com\/wp\/?p=1487","title":{"rendered":"Oracle AQ"},"content":{"rendered":"

Getting Started<\/h2><\/span>\n

Data Dictionary<\/h3><\/span>\n
\r\ndba\/all\/user_evaluation_contexts\r\ndba\/all\/user_evaluation_context_tables\r\ndba\/all\/user_queues\r\ndba\/all\/user_queue_publishers\r\ndba\/all\/user_queue_schedules\r\ndba\/all\/user_queue_tables\r\ndba\/all\/user_rule_sets\r\ndba\/all\/user_rulesets\r\ndba\/all\/user_source\r\ndba\/all\/user_types\r\n<\/pre>\n
Create Users<\/h3><\/span>\n
\r\nCONNECT system\/manager;\r\nDROP USER aqadm CASCADE;\r\nCREATE USER aqadm IDENTIFIED BY aqadm;\r\nGRANT CONNECT, RESOURCE TO aqadm;\r\nGRANT EXECUTE ON DBMS_AQADM TO aqadm;\r\nGRANT Aq_administrator_role TO aqadm;\r\n\r\nDROP USER aq CASCADE;\r\nCREATE USER aq IDENTIFIED BY aq;\r\nGRANT CONNECT, RESOURCE TO aq;\r\nGRANT EXECUTE ON dbms_aq TO aq;\r\nGRANT EXECUTE ON dbms_aqadm TO aq;\r\n<\/pre>\n
Create Q Table and Q of Object Type<\/h3><\/span>\n
\r\nconnect aq\/aq;\r\n\r\nCREATE type aq.Message_typ as object (\r\nsubject VARCHAR2(30),\r\ntext VARCHAR2(80));\r\n\/\r\n\r\nbegin\r\n  DBMS_AQADM.CREATE_QUEUE_TABLE (\r\n  queue_table => 'aq.objmsgs80_qtab',\r\n  queue_payload_type => 'aq.Message_typ');\r\n\r\n   DBMS_AQADM.CREATE_QUEUE (\r\n  queue_name => 'msg_queue',\r\n  queue_table => 'aq.objmsgs80_qtab');\r\n\r\n   DBMS_AQADM.START_QUEUE (\r\n  queue_name => 'msg_queue');\r\nend;\r\n\/\r\n<\/pre>\n
Create a Q Table and Q of Raw Type<\/h3><\/span>\n
\r\nbegin\r\n  DBMS_AQADM.CREATE_QUEUE_TABLE (\r\n  queue_table => 'aq.RawMsgs_qtab',\r\n  queue_payload_type => 'RAW');\r\n\r\n  DBMS_AQADM.CREATE_QUEUE (\r\n  queue_name => 'raw_msg_queue',\r\n  queue_table => 'aq.RawMsgs_qtab');\r\n\r\n  DBMS_AQADM.START_QUEUE (\r\n  queue_name => 'raw_msg_queue');\r\nend;\r\n\/\r\n<\/pre>\n
Create a Prioritized Message Q Table and Q<\/h3><\/span>\n
\r\nbegin\r\n  DBMS_AQADM.CREATE_QUEUE_TABLE (\r\n  queue_table => 'aq.priority_msg',\r\n  sort_list => 'PRIORITY,ENQ_TIME',\r\n  queue_payload_type => 'aq.Message_typ');\r\n\r\n  DBMS_AQADM.CREATE_QUEUE (\r\n  queue_name => 'priority_msg_queue',\r\n  queue_table => 'aq.priority_msg');\r\n\r\n  DBMS_AQADM.START_QUEUE (\r\n  queue_name => 'priority_msg_queue');\r\nend;\r\n\/\r\n<\/pre>\n
Drop Queue<\/h3><\/span>\n
\r\nbegin\r\n   DBMS_AQADM.STOP_QUEUE(\r\n      queue_name => 'TEST_QUEUE');\r\n\r\n   DBMS_AQADM.DROP_QUEUE(\r\n      Queue_name => 'TEST_QUEUE');\r\n \r\n   DBMS_AQADM.DROP_QUEUE_TABLE(\r\n        Queue_table => 'TEST_QUEUE_TAB');\r\nEND;<\/pre>\n
List Topic Subscribers<\/h3><\/span>\n
\r\nset serveroutput on\r\ndeclare\r\n  subList dbms_aqadm.aq$_subscriber_list_t;\r\nbegin\r\n  subList := dbms_aqadm.queue_subscribers('My_Topic');\r\n  dbms_output.put_line('Subscriber count:' || subList.count);\r\n  FOR i IN subList.FIRST .. subList.LAST LOOP\r\n    dbms_output.put_line('Subscriber name: ' || subList(i).name);\r\n  END LOOP;\r\nend;\r\n\/\r\n<\/pre>\n
Remove Subscribers<\/h3><\/span>\n
\r\nset serveroutput on\r\nbegin\r\n  DBMS_AQADM.REMOVE_SUBSCRIBER\r\n      ('My_Topic', SYS.AQ$_AGENT ('Subscriber1', NULL, NULL));\r\nend;\r\n\/\r\n<\/pre>\n
Sample PL\/SQL<\/h3><\/span>\n
\r\nCREATE OR REPLACE PROCEDURE Create_AQ_Queue (\r\n    queueName VARCHAR2, isTopic BOOLEAN)\r\nAS\r\n   queueTableName VARCHAR2(64);\r\nBEGIN\r\n   queueTableName := queueName || '_Tab';\r\n\r\n   DBMS_OUTPUT.put_line ('Create queue table: ' || queueTableName);\r\n   DBMS_AQADM.CREATE_QUEUE_TABLE(\r\n        Queue_table => queueTableName,\r\n        Queue_payload_type => 'SYS.AQ$_JMS_MESSAGE',\r\n        sort_list => 'PRIORITY,ENQ_TIME',\r\n        multiple_consumers => isTopic,\r\n        compatible => '8.1.5');\r\n \r\n   DBMS_OUTPUT.put_line ('Create queue: ' || queueName);\r\n   DBMS_AQADM.CREATE_QUEUE(\r\n      Queue_name => queueName,\r\n      Queue_table => queueTableName);\r\n \r\n   DBMS_OUTPUT.put_line ('Created queue: ' || queueName);\r\nEND;\r\n\/\r\n\r\nCREATE OR REPLACE PROCEDURE Start_AQ_Queue (queueName VARCHAR2)\r\nAS\r\nBEGIN\r\n   DBMS_OUTPUT.put_line ('Starting queue: ' || queueName);\r\n   DBMS_AQADM.START_QUEUE(\r\n      queue_name => queueName);\r\n   DBMS_OUTPUT.put_line ('Started queue: ' || queueName);\r\nEND;\r\n\/\r\n\r\nCREATE OR REPLACE PROCEDURE Stop_AQ_Queue (queueName VARCHAR2)\r\nAS\r\nBEGIN\r\n   DBMS_OUTPUT.put_line ('Stopping queue: ' || queueName);\r\n   DBMS_AQADM.STOP_QUEUE(\r\n      queue_name => queueName);\r\n   DBMS_OUTPUT.put_line ('Stopped queue: ' || queueName);\r\nEND;\r\n\/\r\n\r\nCREATE OR REPLACE PROCEDURE Remove_AQ_Queue (queueName VARCHAR2)\r\nAS\r\nBEGIN\r\n   DBMS_OUTPUT.put_line ('Removing queue: ' || queueName);\r\n   DBMS_AQADM.STOP_QUEUE(\r\n      queue_name => queueName);\r\n \r\n   DBMS_AQADM.DROP_QUEUE(\r\n      Queue_name => queueName);\r\n \r\n   DBMS_AQADM.DROP_QUEUE_TABLE(\r\n        Queue_table => queueName || '_TAB');\r\n   DBMS_OUTPUT.put_line ('Removed: ' || queueName);\r\nEND;\r\n\/\r\n\r\n-- Drop queues\r\nBEGIN\r\n   Remove_AQ_Queue('Test_Queue');\r\n   \r\n   Remove_AQ_Queue('Test_Topic');\r\nEND;\r\n\/\r\n\r\n-- Create queues\r\nBEGIN\r\n   Create_AQ_Queue('Test_Queue', false);\r\n   Start_AQ_Queue('Test_Queue');\r\n   \r\n   Create_AQ_Queue('Test_Topic', true);\r\n   Start_AQ_Queue('Test_Topic');\r\nEND;\r\n\/\r\n<\/pre>\n
* Add\/Remove Subscribers<\/p>\n
\r\n-- Declare a VARRAY type\r\nCREATE OR REPLACE TYPE MyTopics AS VARRAY(16) OF VARCHAR2(64);\r\n\/\r\n\r\n-- Add subscriber procedure\r\nCREATE OR REPLACE PROCEDURE Subscribe_MyTopics \r\n(subscriberName VARCHAR2, subTopics MyTopics)\r\nAS\r\n  subscriber sys.aq$_agent;\r\nBEGIN\r\n  subscriber := sys.aq$_agent(subscriberName, NULL, NULL);\r\n   \r\n  FOR i IN subTopics.FIRST .. subTopics.LAST LOOP\r\n    DBMS_OUTPUT.put_line ('Add subscriber: ' || subscriberName || ' to ITV Topic: ' || subTopics(i));\r\n    DBMS_AQADM.ADD_SUBSCRIBER\r\n    (queue_name => subTopics(i),\r\n     subscriber => subscriber);\r\n  END LOOP;\r\nEND;\r\n\/\r\n\r\n-- Remove subscriber procedure\r\nCREATE OR REPLACE PROCEDURE UnSubscribe_MyTopics \r\n(subscriberName VARCHAR2, subTopics MyTopics)\r\nAS\r\n  subscriber sys.aq$_agent;\r\nBEGIN\r\n  subscriber := sys.aq$_agent(subscriberName, NULL, NULL);\r\n   \r\n  FOR i IN subTopics.FIRST .. subTopics.LAST LOOP\r\n    DBMS_OUTPUT.put_line ('Remove subscriber: ' || subscriberName || ' from ITV Topic: ' || subTopics(i));\r\n    DBMS_AQADM.REMOVE_SUBSCRIBER\r\n    (queue_name => subTopics(i),\r\n     subscriber => subscriber);\r\n  END LOOP;\r\nEND;\r\n\/\r\n\r\n-- Add subscriber\r\nset SERVEROUTPUT ON;\r\nDECLARE\r\n  subTopics MyTopics;\r\nBEGIN\r\n  subTopics := MyTopics('Topic1', 'Topic2', 'Topic3');\r\n\r\n  UnSubscribe_MyTopics('testId', subTopics);\r\n  Subscribe_MyTopics('testId', subTopics);\r\nEND;\r\n\/\r\n\r\n<\/pre>\n
References<\/h1><\/span>\n
* Oracle\u00ae Streams Advanced Queuing User’s Guide and Reference10g Release 2 (10.2)<\/a><\/p>\n
* Oracle\u00ae Streams Advanced Queuing User’s Guide and Reference Release 10.1 Part No. B10785-01<\/a><\/p>\n
* http:\/\/www.akadia.com\/services\/ora_advanced_queueing.html<\/a><\/p>\n
* AQ vs JMS<\/a>
\n* Toad World SQL Reference<\/a>
\n* Oracle DBMS_AQADM<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
Getting Started Data Dictionary dba\/all\/user_evaluation_contexts dba\/all\/user_evaluation_context_tables dba\/all\/user_queues dba\/all\/user_queue_publishers dba\/all\/user_queue_schedules dba\/all\/user_queue_tables dba\/all\/user_rule_sets dba\/all\/user_rulesets dba\/all\/user_source dba\/all\/user_types Create Users CONNECT system\/manager; DROP USER aqadm CASCADE; CREATE USER aqadm IDENTIFIED BY aqadm; GRANT CONNECT, RESOURCE TO aqadm; GRANT EXECUTE ON DBMS_AQADM TO aqadm; GRANT … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[86],"tags":[],"class_list":["post-1487","post","type-post","status-publish","format-standard","hentry","category-aq"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p8cRUO-nZ","_links":{"self":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1487","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1487"}],"version-history":[{"count":13,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1487\/revisions"}],"predecessor-version":[{"id":1928,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=\/wp\/v2\/posts\/1487\/revisions\/1928"}],"wp:attachment":[{"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1487"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1487"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jianmingli.com\/wp\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1487"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}