OraFAQ Forum: Streams & AQ » Cannot Create Queue Table with Custom Message Type

Home » Server Options » Streams & AQ » Cannot Create Queue Table with Custom Message Type

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Cannot Create Queue Table with Custom Message Type [message #213449]

Wed, 10 January 2007 16:23

willmapp
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,
Will

Report message to a moderator

Re: Cannot Create Queue Table with Custom Message Type [message #216794 is a reply to message #213449]

Tue, 30 January 2007 07:36

godora
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
2.Enqueue
3.Dequeue
4.create your original object using
MEMBER FUNCTION GetObject(
self IN AnyData,
obj OUT NOCOPY "<object_type>")
RETURN PLS_INTEGER;

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

Report message to a moderator

Previous Topic:	Problems with Oracle JMS Advanced Queuing
Next Topic:	SET_DML_HANDLER for apply process failing

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Feb 01 23:36:15 CST 2025