How do we determine if the size of the payload/message exceeds 32K?-CodePudding

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;
/

CodePudding user response：

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?