Oracle8i
`
`Application Developer’s Guide - Advanced Queuing
`
`Release 8.1.5
`
`February 1999
`
`Part No. A68005-01
`
`HP_1019_0001
`
`

`
`Application Developer’s Guide - Advanced Queuing, Release 8.1.5
`
`Part No. A68005-01
`
`Copyright © 199x, 1999, Oracle Corporation. All rights reserved.
`
`Primary Author: Denis Raphaely
`
`Contributors: Neerja Bhatt, Sashi Chandrasekaran, Dieter Gawlick, John Gibb, Mohan Kamath,
`Krishnan Meiyyappan, Bhagat Nainani, Goran Olsson, Madhu Reddy, Mary Rhodes, Ashok Saxena,
`Ekrem Soylemez, Alvin To, Rahim Yaseen
`
`Graphic Designer: Valerie Moore
`
`The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other
`inherently dangerous applications. It shall be the licensee’s responsibility to take all appropriate
`fail-safe, backup, redundancy and other measures to ensure the safe use of such applications if the
`Programs are used for such purposes, and Oracle disclaims liability for any damages caused by such
`use of the Programs.
`
`The Programs (which include both the software and documentation) contain proprietary information of
`Oracle Corporation; they are provided under a license agreement containing restrictions on use and
`disclosure and are also protected by copyright, patent, and other intellectual and industrial property
`laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
`
`The information contained in this document is subject to change without notice. If you find any problems
`in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
`document is error free. Except as may be expressly permitted in your license agreement for these
`Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
`electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
`
`If the Programs are delivered to the U.S. Government or anyone licensing or using the Programs on
`behalf of the U.S. Government, the following notice is applicable:
`
`Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
`computer software" and use, duplication, and disclosure of the Programs including documentation, shall
`be subject to the licensing restrictions set forth in the applicable Oracle license agreement. Otherwise,
`Programs delivered subject to the Federal Acquisition Regulations are "restricted computer software"
`and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR 52.227-19,
`Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500 Oracle
`Parkway, Redwood City, CA 94065.
`
`Oracle, Pro*Ada, Pro*COBOL, Pro*FORTRAN, SQL*Loader, SQL*Net and SQL*Plus are registered trade-
`marks of Oracle Corporation, Redwood City, California.
`
`Designer/2000, Developer/2000, Net8, Oracle Call Interface, Oracle7, Oracle8, Oracle8i, Oracle Forms,
`Oracle Parallel Server, PL/SQL, Pro*C, Pro*C/C++ and Trusted Oracle are trademarks of Oracle Corpora-
`tion, Redwood City, California.
`
`All other products or company names are used for identification purposes only, and may be trademarks
`of their respective owners.
`
`HP_1019_0002
`
`

`
`Contents
`
`Send Us Your Comments ................................................................................................................ xvii
`
`Preface.......................................................................................................................................................... xix
`Use Case Diagrams...................................................................................................................... xxiii
`State Diagrams ............................................................................................................................. xxix
`
`1 Introduction
`The Need for Queuing in Messaging Systems ............................................................................. 1-2
`Message Systems .......................................................................................................................... 1-2
`Message Persistence ..................................................................................................................... 1-3
`Features of Advanced Queuing (AQ) ............................................................................................. 1-5
`General Features ........................................................................................................................... 1-5
`ENQUEUE Features ..................................................................................................................... 1-8
`DEQUEUE Features ................................................................................................................... 1-11
`Propagation Features ................................................................................................................. 1-13
`Primary Components of Advanced Queuing (AQ).................................................................... 1-15
`Message........................................................................................................................................ 1-15
`Queue ........................................................................................................................................... 1-15
`Queue Table................................................................................................................................. 1-15
`Agent ............................................................................................................................................ 1-15
`Recipient ...................................................................................................................................... 1-16
`Recipient and Subscription Lists .............................................................................................. 1-16
`Rule............................................................................................................................................... 1-17
`Rule Based Subscriber................................................................................................................ 1-17
`
` iii
`
`HP_1019_0003
`
`

`
`Queue Monitor............................................................................................................................ 1-17
`Modeling Queue Entities ................................................................................................................ 1-18
`Basic Queuing.............................................................................................................................. 1-19
`Illustrating Basic Queuing......................................................................................................... 1-19
`Illustrating Client-Server Communication Using AQ........................................................... 1-21
`Multiple-Consumer Dequeuing of the Same Message.......................................................... 1-23
`Illustrating Multiple-Consumer Dequeuing of the Same Message..................................... 1-24
`Illustrating Dequeuing of Specified Messages by Specified Recipients ............................. 1-26
`Illustrating the Implementation of Workflows using AQ .................................................... 1-28
`Illustrating the Implementation of Publish/Subscribe using AQ ....................................... 1-29
`Message Propagation ................................................................................................................. 1-32
`Illustration of Message Propagation ........................................................................................ 1-34
`Programmatic Environments for Working with AQ .................................................................. 1-35
`AQ and XA ......................................................................................................................................... 1-35
`Compatibility..................................................................................................................................... 1-36
`Restrictions......................................................................................................................................... 1-37
`Auto-commit features in DBMS_AQADM package.............................................................. 1-37
`Collection Types in Message Payloads.................................................................................... 1-37
`Object Type Payload Support in AQ Java API ....................................................................... 1-37
`Synonyms on Queue Tables and Queue ................................................................................. 1-37
`Pluggable Tablespace does not Work For 8.0 Compatible Multiconsumer Queues......... 1-37
`Tablespace point-in-time recovery........................................................................................... 1-38
`Propagation from Object Queues ............................................................................................. 1-38
`Non-Persistent Queues .............................................................................................................. 1-38
`Reference to Demos.......................................................................................................................... 1-39
`
`2 Implementing AQ — A Sample Application
`A Sample Application........................................................................................................................ 2-3
`General Features.................................................................................................................................. 2-4
`System Level Access Control ...................................................................................................... 2-5
`Structured Payload ....................................................................................................................... 2-7
`Queue Level Access Control ....................................................................................................... 2-9
`Non-Persistent Queues .............................................................................................................. 2-10
`Retention and Message History................................................................................................ 2-20
`Publish/Subscribe Support ....................................................................................................... 2-21
`
` iv
`
`HP_1019_0004
`
`

`
`Support for Oracle Parallel Server (OPS)................................................................................ 2-24
`Support for Oracle Parallel Server (OPS) .............................................................................. .. 2-24
`Support for Statistics Views ...................................................................................................... 2-27
`Support for Statistics Views .................................................................................................... .. 2-27
`ENQUEUE Features.......................................................................................................................... 2-28
`ENQUEUE Features ........................................................................................................................ .. 2-28
`Subscriptions and Recipient Lists ............................................................................................ 2-29
`Subscriptions and Recipient Lists .......................................................................................... .. 2-29
`Priority and Ordering of Messages.......................................................................................... 2-31
`Priority and Ordering of Messages ........................................................................................ ..
`2-31
`Time Specification: Delay .......................................................................................................... 2-34
`Time Specification: Delay ........................................................................................................ .. 2-34
`Time Specification: Expiration.................................................................................................. 2-35
`Time Specification: Expiration ................................................................................................ .. 2-35
`Message Grouping...................................................................................................................... 2-37
`Message Grouping.................................................................................................................... .. 2-37
`Asynchronous Notifications ..................................................................................................... 2-39
`Asynchronous Notifications ................................................................................................... .. 2-39
`DEQUEUE Features.......................................................................................................................... 2-46
`DEQUEUE Features ........................................................................................................................ .. 2-46
`Dequeue Methods....................................................................................................................... 2-47
`Dequeue Methods ..................................................................................................................... .. 2-47
`Multiple Recipients .................................................................................................................... 2-50
`Multiple Recipients .................................................................................................................. .. 2-50
`Local and Remote Recipients.................................................................................................... 2-52
`Local and Remote Recipients .................................................................................................. .. 2-52
`Message Navigation in Dequeue.............................................................................................. 2-54
`Message Navigation in Dequeue ............................................................................................ .. 2-54
`Modes of Dequeuing.................................................................................................................. 2-57
`Modes of Dequeuing ................................................................................................................ .. 2-57
`Optimization of Waiting for Arrival of Messages ................................................................. 2-61
`Optimization of Waiting for Arrival of Messages ............................................................... ..
`2-61
`Retry with Delay Interval.......................................................................................................... 2-63
`Retry with Delay Interval ........................................................................................................ .. 2-63
`Exception Handling.................................................................................................................... 2-65
`Exception Handling .................................................................................................................. .. 2-65
`Rule-based Subscription ............................................................................................................ 2-69
`Rule—based Subscription .......................................................................................................... .. 2-69
`Listen Capability......................................................................................................................... 2-72
`Listen Capability ....................................................................................................................... .. 2-72
`Propagation Features........................................................................................................................ 2-76
`Propagation Features ...................................................................................................................... .. 2-76
`Propagation ................................................................................................................................. 2-77
`Propagation ............................................................................................................................... .. 2-77
`Propagation Scheduling ............................................................................................................ 2-78
`Propagation Scheduling .......................................................................................................... .. 2-78
`Propagation of Messages with LOB Attributes...................................................................... 2-82
`Propagation of Messages with LOB Attributes .................................................................... .. 2-82
`Enhanced Propagation Scheduling Capabilities.................................................................... 2-85
`Enhanced Propagation Scheduling Capabilities .................................................................. .. 2-85
`Exception Handling During Propagation............................................................................... 2-87
`Exception Handling During Propagation ............................................................................. .. 2-87
`
`3 Managing Oracle AQ
`3 Managing Oracle AQ
`INIT.ORA Parameter ........................................................................................................................ ..
`3-2
`INIT.ORA Parameter.......................................................................................................................... 3-2
`AQ_TM_PROCESSES .................................................................................................................. 3-2
`AQ_TM_PROCESSES ................................................................................................................ ..
`3-2
`JOB_QUEUE_PROCESSES.......................................................................................................... 3-3
`]OB_QUEUE_PROCESSES ........................................................................................................ ..
`3-3
`Common Data Structures ................................................................................................................ ..
`3-4
`Common Data Structures .................................................................................................................. 3-4
`Object Name .................................................................................................................................. 3-4
`Object Name ................................................................................................................................ ..
`3-4
`Type name ..................................................................................................................................... 3-4
`Type name ................................................................................................................................... ..
`3-4
`Agent .............................................................................................................................................. 3-5
`Agent ............................................................................................................................................ ..
`3-5
`
`v
` v
`
`HP_1019_0005
`
`HP_1019_0005
`
`

`
`AQ Recipient List Type................................................................................................................ 3-6
`AQ Agent List Type...................................................................................................................... 3-6
` AQ Subscriber List Type............................................................................................................. 3-6
`Enumerated Constants in the Administrative Interface.............................................................. 3-7
`Enumerated Constants in the Operational Interface ................................................................... 3-8
`Security ................................................................................................................................................. 3-9
`Security with 8.0 and 8.1 Compatible Queues.......................................................................... 3-9
`Privileges and Access Control .................................................................................................. 3-10
`Roles.............................................................................................................................................. 3-11
`Administrator role ...................................................................................................................... 3-11
`User role ....................................................................................................................................... 3-12
`Access to AQ Object Types........................................................................................................ 3-12
`OCI Applications ........................................................................................................................ 3-12
`Propagation.................................................................................................................................. 3-12
`Performance ....................................................................................................................................... 3-14
`Table and index structures ........................................................................................................ 3-14
`Throughput.................................................................................................................................. 3-14
`Availability .................................................................................................................................. 3-14
`Scalability........................................................................................................................................... 3-15
`Migrating Queue Tables .................................................................................................................. 3-16
`Usage Notes................................................................................................................................. 3-16
`Example: To Upgrade An 8.0 Queue Table To A 8.1-Compatible Queue Table ............... 3-17
`Export and Import of Queue Data ................................................................................................. 3-18
`Exporting Queue Table Data..................................................................................................... 3-18
`Importing Queue Table Data .................................................................................................... 3-19
`Propagation Issues............................................................................................................................ 3-21
`Enterprise Manager Support........................................................................................................... 3-23
`Using XA with AQ........................................................................................................................... 3-23
`Sample DBA Actions as Preparation for Working with AQ .................................................... 3-24
`
`4 Administrative Interface: Basic Operations
`Use Case Model: Administrative Interface — Basic Operations............................................... 4-2
`Create a Queue Table.......................................................................................................................... 4-4
`Usage Notes:.................................................................................................................................. 4-8
`Example: Create a Queue Table Using PL/SQL (DBMS_AQADM Package) ..................... 4-9
`
` vi
`
`HP_1019_0006
`
`

`
`Create a Queue Table [Set Storage Clause].................................................................................. 4-11
`Create a Queue Table [Set Storage Clause] ................................................................................ ..
`4-11
`Alter a Queue Table.......................................................................................................................... 4-12
`Alter a Queue Table ........................................................................................................................ .. 4-12
`Example: Alter a Queue Table Using PL/SQL (DBMS_AQADM Package)...................... 4-13
`Example: Alter a Queue Table Using PL / SQL (DBMS_AQADM Package) .................... .. 4-13
`Usage Notes................................................................................................................................. 4-14
`Usage Notes ............................................................................................................................... .. 4-14
`Drop a Queue Table.......................................................................................................................... 4-15
`Drop a Queue Table ........................................................................................................................ .. 4-15
`Example: Drop a Queue Table Using PL/SQL (DBMS_AQADM Package) ..................... 4-16
`Example: Drop a Queue Table Using PL / SQL (DBMS_AQADM Package) ................... .. 4-16
`Create a Queue .................................................................................................................................. 4-18
`Create a Queue ................................................................................................................................ .. 4-18
`Usage Notes................................................................................................................................. 4-21
`Usage Notes ............................................................................................................................... ..
`4-21
`Example: Create a Queue Using PL/SQL (DBMS_AQADM) ............................................. 4-21
`Example: Create a Queue Using PL/SQL (DBMS_AQADM) ........................................... ..
`4-21
`Create a Non-Persistent Queue...................................................................................................... 4-24
`Create a Non-Persistent Queue .................................................................................................... .. 4-24
`Usage Notes................................................................................................................................. 4-25
`Usage Notes ............................................................................................................................... .. 4-25
`Example: Create a Non-Persistent Queue Using PL/SQL (DBMS_AQADM).................. 4-25
`Example: Create a Non-Persistent Queue Using PL / SQL (DBMS_AQADM) ................ .. 4-25
`Alter a Queue..................................................................................................................................... 4-27
`Alter a Queue ................................................................................................................................... .. 4-27
`Usage Notes................................................................................................................................. 4-29
`Usage Notes ............................................................................................................................... .. 4-29
`Example: Alter a Queue Using PL/SQL (DBMS_AQADM)................................................ 4-29
`Example: Alter a Queue Using PL / SQL (DBMS_AQADM) .............................................. .. 4-29
`Drop a Queue..................................................................................................................................... 4-30
`Drop a Queue ................................................................................................................................... .. 4-30
`Example: Drop a Queue Using PL/SQL (DBMS_AQADM)................................................ 4-31
`Example: Drop a Queue Using PL / SQL (DBMS_AQADM) .............................................. ..
`4-31
`Start a Queue ..................................................................................................................................... 4-32
`Start a Queue ................................................................................................................................... .. 4-32
`Usage Notes................................................................................................................................. 4-33
`Usage Notes ............................................................................................................................... .. 4-33
`Example: Start a Queue using PL/SQL (DBMS_AQADM Package).................................. 4-33
`Example: Start a Queue using PL/SQL (DBMS_AQADM Package) ................................ .. 4-33
`Stop a Queue...................................................................................................................................... 4-34
`Stop a Queue .................................................................................................................................... .. 4-34
`Usage Notes................................................................................................................................. 4-35
`Usage Notes ............................................................................................................................... .. 4-35
`Example: Stop a Queue Using PL/SQL (DBMS_AQADM)................................................. 4-36
`Example: Stop a Queue Using PL/SQL (DBMS_AQADM) ............................................... .. 4-36
`Grant System Privilege.................................................................................................................... 4-37
`Grant System Privilege .................................................................................................................. .. 4-37
`Example: Grant System Privilege Using PL/SQL (DBMS_AQADM)................................ 4-39
`Example: Grant System Privilege Using PL / SQL (DBMS_AQADM) .............................. .. 4-39
`Revoke System Privilege................................................................................................................. 4-40
`Revoke System Privilege ............................................................................................................... .. 4-40
`Example: Revoke System Privilege Using PL/SQL (DBMS_AQADM) ............................. 4-41
`Example: Revoke System Privilege Using PL / SQL (DBMS_AQADM) ........................... ..
`4-41
`Grant Queue Privilege..................................................................................................................... 4-42
`Grant Queue Privilege ................................................................................................................... .. 4-42
`Example: Grant Queue Privilege Using PL/SQL (DBMS_AQADM)................................. 4-43
`Example: Grant Queue Privilege Using PL / SQL (DBMS_AQADM) ............................... .. 4-43
`Revoke Queue Privilege.................................................................................................................. 4-44
`Revoke Queue Privilege ................................................................................................................ .. 4-44
`Usage Notes................................................................................................................................. 4-45
`Usage Notes ............................................................................................................................... .. 4-45
`Example: Revoke Queue Privilege Using PL/SQL (DBMS_AQADM).............................. 4-45
`Example: Revoke Queue Privilege Using PL/SQL (DBMS_AQADM) ............................ .. 4-45
`Add a Subscriber...........................................................