Oracle Database Discussions

Hello everyone,

I need to exercise all of the 32KB PL/SQL AQ limit.

I am trying to create a queue table using a custom message type, and I am consistently receiving a size of data type error.

Here's the code from sqlplus.


create or replace type AQ.RAW_DATATYPE as object (msg_length NUMBER, msg_data VARCHAR(32000));

execute dbms_aqadm.create_queue_table(queue_table => 'AQ.RAW_MSG_QTABLE', queue_payload_type => 'AQ.RAW_DATATYPE');

Here's the error message I receive.

ERROR at line 1:
ORA-00910: specified length too long for its datatype
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 2826
ORA-06512: at "SYS.DBMS_AQADM", line 58
ORA-06512: at line 1

I can create an 'AQ.RAW_DATATYPE' object with a RAW member that is 2000 bytes or a VARCHAR2 that is 4000 bytes, but I have read everywhere that AQ supports 32K using PL/SQL and I need those bytes.

Is there a configuration parameter or something that I am missing here? How come I am limited to such small amounts of data?

I've created a MSG_QUEUE with just a RAW payload but it doesn't work for me. I need a method of knowing how large the actual payload is when I invoke the stored procedure used to dequeue the message and return it through the out parameter using the chosen programming language.

Thanks in advance,
Will