• Overview
  One of our clients had the following requirement:
  • Some notifications had to be triggered during specific events.
  • The notification messages were very long many times exceeding 4000 characters.
  • Some of the notifications were in Japanese language.

• Solution
  The solution could not be incorporated using Oracle Alerts because there were many problems when the message length exceeded a certain limit. Also, the messages had dynamic content and were subject to change in the future. Hence, KBACE came up with a custom solution for this problem. KBACE created a table similar to the Message Dictionary table except that the message was contained in a CLOB column. This allowed huge messages to be stored. To access this table, KBACE created OA pages where a system administrator can edit existing messages or create new messages. The messages could be embedded with dynamic content using tokens. The solution supported up to 15 tokens. KBACE also provided a custom package that takes an alert message, token names and values for the tokens. This custom package could read the CLOB message, break it down into smaller parts, replace the tokens with the actual values and send mails using Oracle's utl smtp packages.
  
  For every notification requirement, Oracle Alerts were created. Instead of sending the notifications themselves, the alerts would just identify the triggering conditions. The alerts would then call the custom package and pass the message name, tokens and token values. The custom package then took care of sending the notifications.
  
  Thus, large messages could be created, maintained and sent. Oracle Alerts functionality was also used to identify the people for whom the notifications had to be sent. Also the messages could contain Japanese language characters since the data is stored in a CLOB.

End of Case Study