oracle-databaseoracle-streams

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


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

Solution

  • 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?