Oracle – Causes of Numeric Overflow When Enqueuing AQ Message

oracleoracle-11g-r2

We create and enqueue messages with various properties, like this:

    message :=  sys.aq$_jms_message.construct(0);
                  message.set_string_property('a', v_a));
                  message.set_int_property('b', v_b);
                  message.set_int_property('c', v_c);
                  message.set_string_property('d', v_d);
    DBMS_AQ.ENQUEUE(queue_name         => SOME_QUEUE,
                    enqueue_options    => queue_options,
                    message_properties => message_properties,
                    payload            => message,
                    msgid              => message_id);

After running this successfully for a long time, we are all of sudden getting

ORA-01426: numeric overflow ORA-06512: at "SYS.DBMS_AQ"

What could possibly cause this?
Running the same enqueuing code against a newly created queue with new queue table produces the same result.

Best Answer

Turns out this problem has nothing to do with message properties, and perhaps not even with the messages themselves. The cause here is actually a bug in Oracle, which you hit when the value of

select max(greatest(object_id, data_object_id)) from dba_objects;

reaches 2^31. There is actually a corresponding patch available from Oracle:

Patch 14837395: AQ CALLS FAIL WITH NUMERIC OVERFLOW WHEN DB OBJECT NUMBERS GREATER THAN 2^31

Related Question