Oracle8i Application Developer's Guide

The Need for Queuing in Messaging Systems

Message Systems

Message Persistence

Features of Advanced Queuing (AQ)

Primary Components of Advanced Queuing (AQ)

Recipient and Subscription Lists

Rule

Rule Based Subscriber

Queue Monitor

Modeling Queue Entities

Basic Queuing

Illustrating Basic Queuing

Illustrating Client-Server Communication Using AQ

Multiple-Consumer Dequeuing of the Same Message

Illustrating Multiple-Consumer Dequeuing of the Same Message

Illustrating Dequeuing of Specified Messages by Specified Recipients

Illustrating the Implementation of Workflows using AQ

Illustrating the Implementation of Publish/Subscribe using AQ

Message Propagation

Illustration of Message Propagation

Programmatic Environments for Working with AQ

AQ and XA

Compatibility

Restrictions

Auto-commit features in DBMS_AQADM package

Collection Types in Message Payloads

Object Type Payload Support in AQ Java API

Synonyms on Queue Tables and Queue

Pluggable Tablespace does not Work For 8.0 Compatible Multiconsumer Queues

Tablespace point-in-time recovery

Propagation from Object Queues

Non-Persistent Queues

Reference to Demos

A Sample Application

General Features

System Level Access Control

Structured Payload

Queue Level Access Control

Non-Persistent Queues

Retention and Message History

Publish/Subscribe Support

Support for Oracle Parallel Server (OPS)

Support for Statistics Views

ENQUEUE Features

Subscriptions and Recipient Lists

Priority and Ordering of Messages

Time Specification: Delay

Time Specification: Expiration

Message Grouping

Asynchronous Notifications

DEQUEUE Features

Dequeue Methods

Multiple Recipients

Local and Remote Recipients

Message Navigation in Dequeue

Modes of Dequeuing

Optimization of Waiting for Arrival of Messages

Retry with Delay Interval

Exception Handling

Rule-based Subscription

Listen Capability

Propagation Features

Propagation

Propagation Scheduling

Propagation of Messages with LOB Attributes

Enhanced Propagation Scheduling Capabilities

Exception Handling During Propagation

INIT.ORA Parameter

AQ_TM_PROCESSES

JOB_QUEUE_PROCESSES

Common Data Structures

Object Name

Type name

Agent

AQ Recipient List Type

AQ Agent List Type

AQ Subscriber List Type

Enumerated Constants in the Administrative Interface

Enumerated Constants in the Operational Interface

Security

Security with 8.0 and 8.1 Compatible Queues

Privileges and Access Control

Roles

Administrator role

User role

Access to AQ Object Types

OCI Applications

Propagation

Performance

Table and index structures

Throughput

Availability

Scalability

Migrating Queue Tables

Usage Notes

Example: To Upgrade An 8.0 Queue Table To A 8.1-Compatible Queue Table

Export and Import of Queue Data

Exporting Queue Table Data

Importing Queue Table Data

Propagation Issues

Enterprise Manager Support

Using XA with AQ

Sample DBA Actions as Preparation for Working with AQ

Use Case Model: Administrative Interface -- Basic Operations

Create a Queue Table

Usage Notes:

Example: Create a Queue Table Using PL/SQL (DBMS_AQADM Package)

Create a Queue Table [Set Storage Clause]

Alter a Queue Table

Example: Alter a Queue Table Using PL/SQL (DBMS_AQADM Package)

Usage Notes

Drop a Queue Table

Example: Drop a Queue Table Using PL/SQL (DBMS_AQADM Package)

Create a Queue

Usage Notes

Example: Create a Queue Using PL/SQL (DBMS_AQADM)

Create a Non-Persistent Queue

Usage Notes

Example: Create a Non-Persistent Queue Using PL/SQL (DBMS_AQADM)

Alter a Queue

Usage Notes

Example: Alter a Queue Using PL/SQL (DBMS_AQADM)

Drop a Queue

Example: Drop a Queue Using PL/SQL (DBMS_AQADM)

Start a Queue

Usage Notes

Example: Start a Queue using PL/SQL (DBMS_AQADM Package)

Stop a Queue

Usage Notes

Example: Stop a Queue Using PL/SQL (DBMS_AQADM)

Grant System Privilege

Example: Grant System Privilege Using PL/SQL (DBMS_AQADM)

Revoke System Privilege

Example: Revoke System Privilege Using PL/SQL (DBMS_AQADM)

Grant Queue Privilege

Example: Grant Queue Privilege Using PL/SQL (DBMS_AQADM)

Revoke Queue Privilege

Usage Notes

Example: Revoke Queue Privilege Using PL/SQL (DBMS_AQADM)

Add a Subscriber

Usage Note:

Example: Add Subscriber Using PL/SQL (DBMS_AQADM)

Example: Add Rule-Based Subscriber Using PL/SQL (DBMS_AQADM)

Alter a Subscriber

Example: Alter Subscriber Using PL/SQL (DBMS_AQADM)

Remove a Subscriber

Usage Notes

Example: Remove Subscriber Using PL/SQL (DBMS_AQADM)

Schedule a Queue Propagation

Usage Notes

Example: Schedule a Propagation Using PL/SQL (DBMS_AQADM)

Unschedule a Queue Propagation

Example: Unschedule a Propagation Using PL/SQL (DBMS_AQADM)

Verify a Queue Type

Example: Verify a Queue Type Using PL/SQL (DBMS_AQADM)

Alter a Propagation Schedule

Example: Alter a Propagation Schedule Using PL/SQL (DBMS_AQADM)

Enable a Propagation Schedule

Example: Enable a Propagation Using PL/SQL (DBMS_AQADM)

Disable a Propagation Schedule

Example: Disable a Propagation Using PL/SQL (DBMS_AQADM)

Usage Notes

Use Case Model: Administrative Interface -- Views

Select All Queue Tables in Database

Select User Queue Tables

Select All Queues in Database

Select All Propagation Schedules

Select Queues for which User has Any Privilege

Select Queues for which User has Queue Privilege

Select Messages in Queue Table

Select Queue Tables in User Schema

Select Queues In User Schema

Select Propagation Schedules in User Schema

Select Queue Subscribers

Usage Notes

Select Queue Subscribers and their Rules

Select the Number of Messages in Different States for the Whole Database

Select the Number of Messages in Different States for Specific Instances

Use Case Model: Operational Interface -- Basic Operations

Enqueue a Message

Usage Notes

Enqueue a Message [Specify Options]

Usage Notes

Enqueue a Message [Specify Message Properties]

Usage Notes

Enqueue a Message [Specify Message Properties [Specify Sender ID]]

Enqueue a Message [Add Payload]

Usage Notes

Example: Enqueue of Object Type Messages

Listen to One (Many) Queue(s)

Usage Notes

Listen to One (Many) Single-Consumer Queue(s)

Example: Listen to Queue(s) Using PL/SQL (DBMS_AQ Package)

Example: Listen to Single-Consumer Queue(s) Using C (OCI)

Listen to One (Many) Multi-Consumer Queue(s)

Example: Listen to Queue(s) Using PL/SQL (DBMS_AQ Package)

Example: Listen to Multi-Consumer Queue(s) Using C (OCI)

Dequeue a Message

Usage Notes

Dequeue a Message from a Single-Consumer Queue [Specify Options]

Usage Notes

Example: Dequeue of Object Type Messages using PL/SQL (DBMS_AQ Package)

Dequeue a Message from a Multi-Consumer Queue [Specify Options]

Register for Notification

Usage Notes

Register for Notification [Specify Subscription Name -- Single-Consumer Queue]

Register for Notification [Specify Subscription Name -- Multi-Consumer Queue]

Example: Register for Notifications For Single-Consumer and Multi-Consumer Queries Using C (OCI)

Introduction

AQDriverManager

getDrivers

getAQSession

registerDriver

APIs/Classes

AQSession

Setup for AQ Examples

Example

AQConstants

AQAgent

AQQueueTableProperty

Constants for Message Grouping

isMulticonsumerEnabled

AQQueueProperty

AQQueueTable

AQQueueAdmin

unschedulePropagation

alterPropagationSchedule

enablePropagationSchedule

disablePropagationSchedule

AQQueue

AQEnqueueOption

AQDequeueOption

AQMessage

AQMessageProperty

AQRawPayload

AQException

AQOracleSQLException

Create Queue Tables and Queues

Create a Queue Table and Queue of Object Type

Create a Queue Table and Queue of Raw Type

Create a Prioritized Message Queue Table and Queue

Create a Multiple-Consumer Queue Table and Queue

Create a Queue to Demonstrate Propagation

Enqueue and Dequeue Of Messages

Enqueue and Dequeue of Object Type Messages Using PL/SQL

Enqueue and Dequeue of Object Type Messages Using Pro*C/C++

Enqueue and Dequeue of Object Type Messages Using OCI

Enqueue and Dequeue of RAW Type Messages Using PL/SQL

Enqueue and Dequeue of RAW Type Messages Using Pro*C/C++

Enqueue and Dequeue of RAW Type Messages Using OCI

Enqueue and Dequeue of RAW Type Messages Using Java

Setup for AQ Examples

Dequeue of Messages Using Java

Dequeue of Messages in Browse Mode Using Java

Enqueue and Dequeue of Messages by Priority Using PL/SQL

Enqueue of Messages with Priority Using Java

Dequeue of Messages after Preview by Criterion Using PL/SQL

Enqueue and Dequeue of Messages with Time Delay and Expiration Using PL/SQL

Enqueue and Dequeue of Messages by Correlation and Message ID Using Pro*C/C++

Enqueue and Dequeue of Messages by Correlation and Message ID Using OCI

Enqueue and Dequeue of Messages to/from a Multiconsumer Queue Using PL/SQL

Enqueue and Dequeue of Messages to/from a Multiconsumer Queue using OCI

Enqueue and Dequeue of Messages Using Message Grouping Using PL/SQL

Enqueuing and Dequeuing Object Type Messages That Contain LOB Attributes Using PL/SQL

Propagation

Enqueue of Messages for remote subscribers/recipients to a Multiconsumer Queue and Propagation Scheduling Using PL/SQL

Manage Propagation From One Queue To Other Queues In The Same Database Using PL/SQL

Manage Propagation From One Queue To Other Queues In Another Database Using PL/SQL

Unscheduling Propagation Using PL/SQL

Drop AQ Objects

Revoke Roles and Privileges

Deploy AQ with XA

AQ and Memory Usage

Create_types.sql : Create Payload Types and Queues in Scott's Schema

Enqueue Messages (Free Memory After Every Call) Using OCI

Enqueue Messages (Reuse Memory) Using OCI

Dequeue Messages (Free Memory After Every Call) Using OCI

Dequeue Messages (Reuse Memory) Using OCI

tkaqdoca.sql: Script to Create Users, Objects, Queue Tables, Queues & Subscribers

tkaqdocd.sql: Examples of Administrative and Operational Interfaces

tkaqdoce.sql: Operational Examples

tkaqdocp.sql: Examples of Operational Interfaces

tkaqdocc.sql: Clean-Up Script

Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Introduction

2 Implementing AQ -- A Sample Application

3 Managing Oracle AQ

4 Administrative Interface: Basic Operations

5 Administrative Interface: Views

6 Operational Interface: Basic Operations

7 Advanced Queuing -- Java API

8 Oracle Advanced Queuing by Example

A Scripts for Implementing 'BooksOnLine'

Index