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 reader should consult the Oracle documentation (Oracle® Streams Advanced Queuing User’s Guide and Oracle® Database PL/SQL Packages and Types Reference) prior to beginning application development.

Oracle AQ provides PL/SQL, OCI, JMS and SOAP APIs. While the all offer virtually identical functionality, this series refers only to the PL/SQL packages, DBMS_AQ and DBMS_AQADM.

What is Messaging?

Messaging is the ability to send a message containing data from one application/process to another application/process. It is a widely used technique in distributed systems, particularly high volume OLTP systems. Unlike client server applications, which are typically synchronous, messaging is typically asynchronous, i.e. the sender, referred to as the producer, is not blocked waiting for a reply from the recipient(s), referred to as consumer(s ). Oracle Advance Queuing (AQ) does not support synchronous messaging.

Messaging has many uses and advantages. It allows applications and systems to communicate and co-operate in an API independent manner. An order entry system may send a message containing order information to a fulfillment system without requiring access to internal APIs. The same message may also simultaneously be routed to an inventory management system, a customer support application, an email acknowledgment application, etc.

Messages are placed into queues, called enqueuing. The enqueuing applications are called the producers. There is typically no restriction on the number of producers for a given queue.

The application data portion of the message is referred to as the payload.

Messages are read and removed from the queue by dequeuing the message.   Applications dequeuing messages are referred to as consumers.

There are three general categories of messaging:

• Single consumer, a.k.a., point-to-point – a message is dequeued by a single consumer
• Multicast – the producer effectively names designated consumers for the message
• Broadcast – consumers may dynamically gain access to a message queue by subscribing

A robust messaging systems supports a wide variety of features in addition to those describe above. These include:

• Error handling
• Timeouts and expirations
• Enqueuing/dequeuing of messages as a group
• Dequeueing messages by criteria other than FIFO, including, but not limited to :
• Enqueue time
• Priority
• Contents of messages
• Reliability
• Propagation – pushing messages to destinations
• Other queues
• Other databases
• Other messaging systems (JMS, middleware, gateways)
• Retention of messages and history of actions
• Non-repudiation
• Logging
• Performance evaluation
• Warehousing
• Wide range of message content data types (aka payload), including :
• Text
• XML
• BLOB, LOB, CLOB
• Structured records
• Notification to consumers of message availability
• Guaranteed delivery
• High performance

Oracle AQ provides all of this functionality.

In addition, Oracle AQ also provides the ability to browse messages without dequeuing.

In Part 2, we’ll take a closer look at queue types.

 

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 (col_name DEFAULT default_value NULL) is now dictionary based.   In other words, unlike 11G, you can now add a NULL column with a default value to a table without backfilling existing rows and updating all of the data blocks.  11G only supported ALTER TABLE table_name ADD (col_name DEFAULT default_value NOT NULL) as dictionary based.

Let’s do a quick test.

CREATE TABLE JACOBS.table_initial_null_default_col
(
ID NUMBER,
initial_null_default_col VARCHAR2(50) DEFAULT '12345678901234567890123456789012345678901234567890'
);
ALTER TABLE JACOBS.table_initial_null_default_col ADD (
CONSTRAINT table_initial_null_default__PK
PRIMARY KEY
(ID)
ENABLE VALIDATE);
INSERT INTO TABLE_INITIAL_NULL_DEFAULT_COL (id)
( SELECT ROWNUM
FROM DUAL
CONNECT BY LEVEL <= 100000);
COMMIT;


Verifying the data:

SELECT *
FROM TABLE_INITIAL_NULL_DEFAULT_COL
WHERE ID < 10;

ID,INITIAL_NULL_DEFAULT_COL
1,12345678901234567890123456789012345678901234567890
2,12345678901234567890123456789012345678901234567890
3,12345678901234567890123456789012345678901234567890
4,12345678901234567890123456789012345678901234567890
5,12345678901234567890123456789012345678901234567890
6,12345678901234567890123456789012345678901234567890
7,12345678901234567890123456789012345678901234567890
8,12345678901234567890123456789012345678901234567890
9,12345678901234567890123456789012345678901234567890

Gathering statistics on the table gives us 847 data blocks.

Let’s add a new column:

ALTER TABLE JACOBS.TABLE_INITIAL_NULL_DEFAULT_COL
ADD (
NEW_NULL_DEFAULT_COL VARCHAR2 (50)
DEFAULT '12345678901234567890123456789012345678901234567890' NULL);

Verifying the data:

SELECT *
FROM TABLE_INITIAL_NULL_DEFAULT_COL
WHERE ID < 10;

ID,INITIAL_NULL_DEFAULT_COL,NEW_NULL_DEFAULT_COL
1,12345678901234567890123456789012345678901234567890,12345678901234567890123456789012345678901234567890
2,12345678901234567890123456789012345678901234567890,12345678901234567890123456789012345678901234567890
3,12345678901234567890123456789012345678901234567890,12345678901234567890123456789012345678901234567890
4,12345678901234567890123456789012345678901234567890,12345678901234567890123456789012345678901234567890
5,12345678901234567890123456789012345678901234567890,12345678901234567890123456789012345678901234567890
6,12345678901234567890123456789012345678901234567890,12345678901234567890123456789012345678901234567890
7,12345678901234567890123456789012345678901234567890,12345678901234567890123456789012345678901234567890
8,12345678901234567890123456789012345678901234567890,12345678901234567890123456789012345678901234567890
9,12345678901234567890123456789012345678901234567890,12345678901234567890123456789012345678901234567890

Gathering statistics, we still have 847 data blocks.

So what happens if we truncate the tables and re-insert 100K rows?  (We expected the default values to actually be in the data blocks).
TRUNCATE TABLE TABLE_INITIAL_NULL_DEFAULT_COL;
INSERT INTO TABLE_INITIAL_NULL_DEFAULT_COL (id)
( SELECT ROWNUM
FROM DUAL
CONNECT BY LEVEL <= 100000);
COMMIT;

Gathering statistics gives us 1630 blocks, as we would expect!

Note that the behavior for a NULLABLE column with DEFAULT values is that the column IS NULL only when NULL is INSERTed.

ODTUG at 20 – Memories and Reminiscing

2017 marks the 20th anniversary of ODTUG, originally the Oracle CASE Special Interest Group (OCSIG).

In 1994, I inherited the presidency of OCSIG.  It was a very small group of practitioners of what was then the Oracle CASE tool.   Our conference was held in Keystone, Colorado. We shared the hotel with the Oracle Application User Group (OAUG), which wasn’t much bigger.  OCSIG attendance was probably less than 100 and OAUG less than 250.

How times have changed!   OAUG’s conference (QUEST) is now huge, and ODTUG is still considered the Goldilocks of user group conferences, not too big and not too small!

The technology in those day was very different.  Client server and departmental computing were the rage.   Windows was making rapid advances.   Oracle CASE, originally requiring either a UNIX work station (no LINUX in those days) or a hybrid IBM OS/2 windows environment had become Oracle Designer, totally Windows base.  Java was barely more than a cup of coffee…

The optimizer was rule based; you could dramatically change performance by swapping the order of predicates in the WHERE clause.   You could count the CPUs on one hand.

Oracle Forms and Oracle Reports were the main development tools; Designer generated Forms and Report (and SQL*Plus queries) from diagrams and host of preference setting.

The internet was not even a player.  UNIX forums, CompuServe and hosted email lists were the online means of communication.  The founders of Google were still in grade school.  I think 1200 baud modems were as good as it got!

While Designer was very powerful, it also required a great deal of training and was not cheap.   Many developers wanted to be able to work directly in Forms and Reports, and the rapid advance in capabilities of the tools deserved their own focus and attention.

As president, with prior experience with non-Oracle user groups, I set the tone that the group should be self-sustaining and run as a business with professional support.   As with any business, growth was essential.

After much disussion, the board decided that we should expand the scope of OCISG to include the Oracle development tools as stand-alone products, not just generated results from Designer.  Much to our surprise, we encountered some resistance from the “CASE purists”, but we proceeded.   It was announced at the conference is San Diego in June, 1997.

It has been a tremenous success, thanks to the hard work and vision of its leaders, board member and staff of Your Conference Connection.

The original technologies are ancient history.  But ODTUG has changed with the times.   APEX, Big Data, the database itself, EPM, Java and SQL Developer are the current technology.   (Sadly, IMO, many of the great capabilities of Designer have been lost and are only slowly being resurrected in SQl Developer).

ODTUG has always been known as having great conferences, and I’m sure the 20th Anniversary conference, Kscope17 will be very special.  I hope to see you there!