According to Oracle's Streams Advanced Queuing User's Guide and Reference: "To store a payload of type RAW, Oracle Streams AQ creates a queue table with LOB column as the payload repository. The maximum size of the payload is determined by which programmatic interface you use to access Oracle Streams AQ. For PL/SQL, Java and precompilers the limit is 32K; for the OCI the limit is 4G."
So my question is how can we determine if the size of the payload/message exceeds 32K?
The existing Oracle procedure looks like this:
CREATE OR REPLACE procedure PRC_ordercreated(P_MSG in clob, P_MSGID out raw)
is
V_ENQUEUEOPTIONS SYS.DBMS_AQ.ENQUEUE_OPTIONS_T;
V_MESSAGEPROPERTIES SYS.DBMS_AQ.MESSAGE_PROPERTIES_T;
V_QUEUENAME varchar2(35) := 'QUE_ordercreated';
begin
V_MESSAGEPROPERTIES.USER_PROPERTY := SYS.ANYDATA.CONVERTTIMESTAMPTZ(systimestamp);
/* when the payload message exceeds 32K, the message will be stored in a separate table
*/
SYS.DBMS_AQ.ENQUEUE(
QUEUE_NAME => V_QUEUENAME,
PAYLOAD => SYS.UTL_RAW.CAST_TO_RAW(P_MSG),
ENQUEUE_OPTIONS => V_ENQUEUEOPTIONS,
MESSAGE_PROPERTIES => V_MESSAGEPROPERTIES,
MSGID => P_MSGID);
insert into QUEUE_OVERSIZEDMESSAGE(
MSGID,
LARGEMESSAGE)
values (
P_MSGID,
P_MSG);
end;
/
[UPDATE] With the help of @kfinity's answer, please find my final solution below:
CREATE OR REPLACE procedure PRC_ENQUEUE(P_MSG in clob, P_MSGID out raw)
is
V_ENQUEUEOPTIONS SYS.DBMS_AQ.ENQUEUE_OPTIONS_T;
V_MESSAGEPROPERTIES SYS.DBMS_AQ.MESSAGE_PROPERTIES_T;
V_QUEUENAME varchar2(16) := 'QUE_ORDERCREATED';
V_MAXPAYLOADSIZE number := 32000;
begin
V_MESSAGEPROPERTIES.USER_PROPERTY := SYS.ANYDATA.CONVERTTIMESTAMPTZ(systimestamp);
/* When the payload message exceeds 32K, the message will be stored in a separate table
*/
if SYS.UTL_RAW.LENGTH(SYS.UTL_RAW.CAST_TO_RAW(P_MSG)) > V_MAXPAYLOADSIZE then
SYS.DBMS_AQ.ENQUEUE(
QUEUE_NAME => V_QUEUENAME,
PAYLOAD => SYS.UTL_RAW.CAST_TO_RAW('IsLargeMessage'),
ENQUEUE_OPTIONS => V_ENQUEUEOPTIONS,
MESSAGE_PROPERTIES => V_MESSAGEPROPERTIES,
MSGID => P_MSGID);
insert into QUEUE_LARGEMESSAGE(
MSGID,
LARGEMESSAGE,
CREATIONDATETIME,
LASTMODIFICATIONDATETIME)
values (
P_MSGID,
P_MSG,
systimestamp,
systimestamp);
else
SYS.DBMS_AQ.ENQUEUE(
QUEUE_NAME => V_QUEUENAME,
PAYLOAD => SYS.UTL_RAW.CAST_TO_RAW(P_MSG),
ENQUEUE_OPTIONS => V_ENQUEUEOPTIONS,
MESSAGE_PROPERTIES => V_MESSAGEPROPERTIES,
MSGID => P_MSGID);
end if;
end;
/
I would add an IF statement to check the length of the raw variable. The max size is 32767.
CREATE OR REPLACE procedure PRC_ordercreated(P_MSG in clob, P_MSGID out raw)
is
V_ENQUEUEOPTIONS SYS.DBMS_AQ.ENQUEUE_OPTIONS_T;
V_MESSAGEPROPERTIES SYS.DBMS_AQ.MESSAGE_PROPERTIES_T;
V_QUEUENAME varchar2(35) := 'QUE_ordercreated';
begin
V_MESSAGEPROPERTIES.USER_PROPERTY := SYS.ANYDATA.CONVERTTIMESTAMPTZ(systimestamp);
/* when the payload message exceeds 32K, the message will be stored in a separate table
*/
if SYS.UTL_RAW.LENGTH(SYS.UTL_RAW.CAST_TO_RAW(P_MSG)) < 32768 then
SYS.DBMS_AQ.ENQUEUE(
QUEUE_NAME => V_QUEUENAME,
PAYLOAD => SYS.UTL_RAW.CAST_TO_RAW(P_MSG),
ENQUEUE_OPTIONS => V_ENQUEUEOPTIONS,
MESSAGE_PROPERTIES => V_MESSAGEPROPERTIES,
MSGID => P_MSGID);
else
insert into QUEUE_OVERSIZEDMESSAGE(
MSGID,
LARGEMESSAGE)
values (
P_MSGID,
P_MSG);
end if;
end;
/
You might need to adjust this if you want to still want to add a message to the queue when the payload is too large, maybe with a placeholder payload instead?