Advanced Queueing offers great queueing functionality built into Oracle Database. When you want to create a queue, Oracle Database will create several supporting objects. Depending on how you use Advanced Queueing and Oracle Database, these objects might change.

Let’s investigate a case that came up during a project.

The Situation

After I recreated a queue table, some of the underlying objects changed definition. Specifically, columns that apparently contained user information changed from a NUMBER to a VARCHAR2.

Object Name	Column Name	Data Type Before	Data Type After
AQ$<queue_table>	ENQ_USER_ID	NUMBER	VARCHAR2
AQ$<queue_table>	DEQ_USER_ID	NUMBER	VARCHAR2
AQ$_<queue_table>_F	ENQ_UID	NUMBER	VARCHAR2
AQ$_<queue_table>_F	DEQ_UID	NUMBER	VARCHAR2
AQ$_<queue_table>_H	DEQUEUE_USER	NUMBER	VARCHAR2
AQ$_<queue_table>_L	DEQUEUE_USER	NUMBER	VARCHAR2

The column data type changed from NUMBER to VARCHAR2. I created the queue tables using DBMS_AQADM and Oracle Database created the AQ$ objects recursively.

Is this something to be worried about?

Why Does the Data Type Change

Advanced Queueing has been around for a while and it has evolved. To control the behavior of Advanced Queueing, you can use the compatible parameter when you create queue tables.

In Oracle Database 19c, you can set compatible parameter on a queue table to one of the following:

• 8.0
• 8.1
• 10.0

When you create a queue table, it is an optional parameter:

SQL> begin
        dbms_aqadm.create_queue_table (
           ...
           compatible => '10.0'
        );
     end;

If you don’t explicitly specify a compatible setting, it is derived from the database instance parameter compatible.

You can find the compatible setting of a queue table using:

SQL> select queue_table, compatible from user_queue_tables;

In the documentation, you can find information on which functionality gets enabled by which compatible setting. In this case, the following is of interest:

Mixed case (upper and lower case together) queue names, queue table names, and subscriber names are supported if database compatibility is 10.0

When you set compatible on the queue table to 10.0 there is better support for certain user names (subscriber names), and that requires a different data type on the underlying objects.

How to Solve the Problem

There are two options:

1. You can recreate the queue tables using the same compatible setting. You start by querying USER_QUEUE_TABLES to find the compatible setting. Then, use DBMS_AQADM.CREATE_QUEUE_TABLE to recreate the queue and remember to specify the correct compatible setting.
2. You can adapt the newest compatible setting on your queues. The underlying objects change. You can use all the features of Advanced Queueing. You should test your application and ensure it works with the new setting.

I recommend option 2. It is uses the default setting for compatible. The default setting has been around in many years, so it is thoroughly tested and I assume that most customers use this configuration.

You Can Migrate Old Queue Tables

You can migrate old queues in your Oracle Database. Any queues that don’t have compatible set to 10.0, you can migrate to the newest compatible setting:

SQL> begin
        dbms_aqadm.migrate_queue_table(..., compatible => '10.0.0');
     end;

Now you can start to use all the features in Advanced Queueing.

You can query the data dictionary to find old queues in your Oracle Database:

SQL> select queue_table, compatible
     from user_queue_tables
     where compatible != '10.0.0';

Appendix

Thanks to oracle-base.com for supplying the starting point for my test case.

Test Case

conn / as sysdba
--create user and grant privileges
drop user appuser cascade;
create user appuser identified by appuser;
grant dba to appuser;

conn appuser/appuser
--type used for queue payload
create type car_type as object (
  name            varchar2(20),
  color           varchar2(10)
);
/

--get the database instance compatible setting
--used to derive the queue table compatible setting
--if not specified
select value from v$parameter where name='compatible';

begin
   --create queue table without expliciti 'compatible'
   --compatible should be 10.0.0
   dbms_aqadm.create_queue_table (
      queue_table            => 'APPUSER.CAR_QUEUE_TAB',
      queue_payload_type     => 'APPUSER.CAR_TYPE');
   --create new queue table with lower compatible setting
   dbms_aqadm.create_queue_table (
      queue_table            => 'APPUSER.CAR_QUEUE_TAB8',
      queue_payload_type     => 'APPUSER.CAR_TYPE',
      compatible => '8.0');
end;
/

--verify queue table compatible setting
select queue_table, compatible from user_queue_tables;