Home » Server Options » Streams & AQ » Cannot Create Queue Table with Custom Message Type
Cannot Create Queue Table with Custom Message Type [message #213449] Wed, 10 January 2007 16:23 Go to next message
Messages: 1
Registered: January 2007
Junior Member
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,

Re: Cannot Create Queue Table with Custom Message Type [message #216794 is a reply to message #213449] Tue, 30 January 2007 07:36 Go to previous message
Messages: 2
Registered: February 2005
Junior Member
Hi Will,
You'd better use SYS.ANYDATA as payload type. Than
1.create your payload using constructors of anydata:
STATIC FUNCTION ConvertObject(obj IN "<object_type>") RETURN AnyData
4.create your original object using
self IN AnyData,
obj OUT NOCOPY "<object_type>")

This approach makes your queue more flexible for using any kind of user defined types.
See Supplied PL/SQL Packages and Types Reference for details on ANYDATA type.

Regards, Attila
Previous Topic: Problems with Oracle JMS Advanced Queuing
Next Topic: SET_DML_HANDLER for apply process failing
Goto Forum:

Current Time: Wed May 31 14:26:32 CDT 2023