Oracle Posts

Developing with Oracle Advanced Queuing – Part 1, Intro to Messaging

Introduction This series of posts isintended to provide the reader  with an understanding of the basic features and capabilities of Oracle Advanced Queuing (AQ) for consideration in application development. It does not cover all of the features and capabilities of Oracle AQ. The reader is cautioned that the author is neither omniscient nor infallible.   The … Continue reading “Developing with Oracle Advanced Queuing – Part 1, Intro to Messaging”

Developing with Oracle Advanced Queuing – Part 2, Queue Types

In Part I, I provided a introduction to messaging.   In this section, I present additional basic concepts. Queue Types Oracle AQ provides the three types of messaging describe above via two basic types of queues, single consumer queues and multi-consumer queues.  A multi-consumer queue may provide both multicast and broadcast capabilities. All queues allow … Continue reading “Developing with Oracle Advanced Queuing – Part 2, Queue Types”

Developing with Oracle Advanced Queuing – Part 3, Creating AQ Tables

Advanced Queuing (AQ) Tables An AQ table is an abstract object type, which may be implemented by one or more underlying tables, indexes and index organized tables depending on whether the AQ table supports single or multi-consumer queues. An AQ table typically holds one or more queues, which can be created and destroyed dynamically. Multi-consumer … Continue reading “Developing with Oracle Advanced Queuing – Part 3, Creating AQ Tables”

Developing with Oracle Advanced Queuing – Part 4, Creating Queues

Queues are created via: DBMS_AQADM.CREATE_QUEUE ( queue_name IN VARCHAR2, queue_table IN VARCHAR2, queue_type IN BINARY_INTEGER DEFAULT NORMAL_QUEUE, max_retries IN NUMBER DEFAULT NULL, retry_delay IN NUMBER DEFAULT 0, retention_time IN NUMBER DEFAULT 0, dependency_tracking IN BOOLEAN DEFAULT FALSE, comment IN VARCHAR2 DEFAULT NULL, auto_commit IN BOOLEAN DEFAULT TRUE); The parameters are described below: queue_name – the … Continue reading “Developing with Oracle Advanced Queuing – Part 4, Creating Queues”

Developing with Oracle Advanced Queuing – Part 5, Enqueuing Messages

There is a wide range of options for enqueuing messages.  These options include, but are not limited to: Enqueuing a single message. Enqueuing an array of messages. Message Grouping, which treats all messages enqueued in a single transaction as a group. Sender Identification. Time Specification and Scheduling of message delivery. Correlation Identifier, which allows multiple … Continue reading “Developing with Oracle Advanced Queuing – Part 5, Enqueuing Messages”

Developing with Oracle Advanced Queuing – Part 6, Dequeuing Messages

Now that we know how to create tables and queues and enqueue messges, let’s look at how to dequeue them! Dequeuing Features Oracle AQ provides very high performance and functionality.  Key features include: Concurrent dequeues Multiple dequeue methods and options Array dequeue Message navigation Waiting for messages Retries with delays Transaction protection Exception queues DBMS_AQ.DEQUEUE … Continue reading “Developing with Oracle Advanced Queuing – Part 6, Dequeuing Messages”

Developing with Oracle Advanced Queuing – Part 7, Message Propagation

Propagation Messages may be pushed to other queues via propagation.  Those queues typically, but not always, exist in another database or an external messaging system; the latter is beyond the scope of this paper.  Propagation may also be to queues in the same database.  The messages are ultimately processed by consumers of the destination queue(s); … Continue reading “Developing with Oracle Advanced Queuing – Part 7, Message Propagation”

Developing with Oracle Advanced Queuing – Part 8, Structures, Performance Tips and Misc.

AQ Table Structures A multi-consumer AQ table has 7 underlying tables, both heap and index organized.  The main table with message data for all queues has the same name as specified in CREATE_QUEUE_TABLE, e.g. ORDERS_QUEUETABLE.  Other tables have names beginning with AQ$, e.g. AQ$_ORDERS_QUEUETABLE_H A single consumer AQ table creates a single table with main … Continue reading “Developing with Oracle Advanced Queuing – Part 8, Structures, Performance Tips and Misc.”

Most Overlooked New Oracle 12c SQL Feature?

I truly thought I had heard every presentation on Oracle 12c, and read everything available… So I was quite surprised to accidentally discover a new feature that I had no recollection of having heard mentioned (not that my memory is perfect or that it was mentioned and it just didn’t register). Specifically, ALTER TABLE table_name ADD … Continue reading “Most Overlooked New Oracle 12c SQL Feature?”

UNDERSTANDING THE DEVELOPER INEXPERIENCED IN ORACLE TECHNOLOGIES (DIO)

An anti-pattern is a common design, SQL or application development practice that results in poor performance.   This post discusses why anti-patterns are so common in today’s information technology. Even though OO theory is in fact older than relational theory, originating with Simula-67 in 1967, versus the early ‘70s for Codd’s and Date’s works, OO didn’t … Continue reading “UNDERSTANDING THE DEVELOPER INEXPERIENCED IN ORACLE TECHNOLOGIES (DIO)”