XE JMS_TEXT_MESSAGE Replacement

Submitted by code_admin on Fri, 07/20/2018 - 12:50

JMS_TEXT_MESSAGE isn't available as a type in Oracle XE databases. This code back ports it.

These are replacement objects for JMS_TEXT_MESSAGE which isn't availiable in Oracle XE
This can be used in a create queue table statement:

  1.     dbms_aqadm.create_queue_table(
  2.          queue_table          => 'webserv.TEST',
  3.          multiple_consumers   => false,
  4.          queue_payload_type   => 'sys.RJM_JMS_TEXT_MESSAGE'
  5.     );
  6. <plsql>
  7. Run as SYS
  8. <plsql>
  9. create or replace
  10. TYPE RJM_JMS_TEXT_STR_PROP_v AS VARRAY(50) OF VARCHAR2(255);
  1. create or replace
  2. type     RJM_JMS_TEXT_MESSAGE AS OBJECT
  3. (
  4.   m_payload CLOB,
  5. --  m_str_prop_names RJM_JMS_TEXT_STR_PROP_v,
  6. --  m_str_prop_values RJM_JMS_TEXT_STR_PROP_v,
  7.   m_properties XMLTYPE,
  8.   m_str_props_siz number,
  9.   CONSTRUCTOR FUNCTION RJM_JMS_TEXT_MESSAGE RETURN SELF AS RESULT,
  10.   STATIC FUNCTION construct RETURN rjm_jms_text_message,
  11.   MEMBER PROCEDURE set_text ( payload IN VARCHAR2 ),
  12.   MEMBER PROCEDURE set_text ( payload IN CLOB ),
  13.   MEMBER PROCEDURE get_text ( payload OUT VARCHAR2 ),
  14.   MEMBER PROCEDURE get_text ( payload OUT CLOB ),
  15.   MEMBER PROCEDURE set_string_property (
  16.                 property_name   IN      VARCHAR,
  17.                 property_value  IN      VARCHAR ),
  18.   MEMBER FUNCTION get_string_property ( property_name   IN      VARCHAR)
  19.   RETURN   VARCHAR
  20.  
  21. /*
  22. Type created to emulate AQ$_JMS_TEXT_MESSAGE on XE Database
  23. Non-implemented functions below
  24.  
  25.   header    aq$_jms_header,
  26.   text_len  int,
  27.   text_vc   varchar2(4000),
  28.   text_lob  clob,
  29.   MEMBER PROCEDURE set_replyto (replyto IN      sys.aq$_agent),
  30.   MEMBER PROCEDURE set_type (type       IN      VARCHAR ),
  31.   MEMBER PROCEDURE set_userid (userid   IN      VARCHAR ),
  32.   MEMBER PROCEDURE set_appid (appid     IN      VARCHAR ),
  33.   MEMBER PROCEDURE set_groupid (groupid IN      VARCHAR ),
  34.   MEMBER PROCEDURE set_groupseq (groupseq       IN      int ),
  35.   MEMBER PROCEDURE clear_properties ,
  36.   MEMBER PROCEDURE set_boolean_property (
  37.                 property_name   IN      VARCHAR,
  38.                 property_value  IN      BOOLEAN ),
  39.   MEMBER PROCEDURE set_byte_property (
  40.                 property_name   IN      VARCHAR,
  41.                 property_value  IN      int ),
  42.   MEMBER PROCEDURE set_short_property (
  43.                 property_name   IN      VARCHAR,
  44.                 property_value  IN      int ),
  45.   MEMBER PROCEDURE set_int_property (
  46.                 property_name   IN      VARCHAR,
  47.                 property_value  IN      int ),
  48.   MEMBER PROCEDURE set_long_property (
  49.                 property_name   IN      VARCHAR,
  50.                 property_value  IN      NUMBER ),
  51.   MEMBER PROCEDURE set_float_property (
  52.                 property_name   IN      VARCHAR,
  53.                 property_value  IN      FLOAT ),
  54.   MEMBER PROCEDURE set_double_property (
  55.                 property_name   IN      VARCHAR,
  56.                 property_value  IN      DOUBLE PRECISION ),
  57.   MEMBER FUNCTION get_replyto RETURN sys.aq$_agent,
  58.   MEMBER FUNCTION get_type RETURN VARCHAR,
  59.   MEMBER FUNCTION get_userid RETURN VARCHAR,
  60.   MEMBER FUNCTION get_appid RETURN VARCHAR,
  61.   MEMBER FUNCTION get_groupid RETURN VARCHAR,
  62.   MEMBER FUNCTION get_groupseq RETURN int,
  63.   MEMBER FUNCTION get_boolean_property ( property_name   IN      VARCHAR)
  64.   RETURN   BOOLEAN,
  65.   MEMBER FUNCTION get_byte_property ( property_name   IN      VARCHAR)
  66.   RETURN   int,
  67.   MEMBER FUNCTION get_short_property ( property_name   IN      VARCHAR)
  68.   RETURN   int,
  69.   MEMBER FUNCTION get_int_property ( property_name   IN      VARCHAR)
  70.   RETURN   int,
  71.   MEMBER FUNCTION get_long_property ( property_name   IN      VARCHAR)
  72.   RETURN   NUMBER,
  73.   MEMBER FUNCTION get_float_property ( property_name   IN      VARCHAR)
  74.   RETURN   FLOAT,
  75.   MEMBER FUNCTION get_double_property ( property_name   IN      VARCHAR)
  76.   RETURN   DOUBLE PRECISION,
  77. */
  78. )
  1. create or replace
  2. type body     RJM_JMS_TEXT_MESSAGE  AS
  3.  
  4.   CONSTRUCTOR FUNCTION RJM_JMS_TEXT_MESSAGE RETURN SELF AS RESULT
  5.   AS
  6.   BEGIN
  7.     self.m_str_props_siz := 0;
  8.     self.m_properties := XMLTYPE('<a></a>');
  9.     return;
  10.   END;
  11.   STATIC FUNCTION construct RETURN RJM_JMS_TEXT_MESSAGE
  12.   IS
  13.   BEGIN
  14.     return NEW RJM_JMS_TEXT_MESSAGE;
  15.   END;
  16.  
  17.  
  18.   MEMBER PROCEDURE set_text ( payload IN VARCHAR2 )
  19.   IS
  20.   BEGIN
  21.     self.m_payload := to_clob(payload);
  22.   END;
  23.  
  24.   MEMBER PROCEDURE set_text ( payload IN CLOB )
  25.   IS
  26.   BEGIN
  27.     self.m_payload := payload;
  28.   END;
  29.  
  30.   MEMBER PROCEDURE get_text ( payload OUT VARCHAR2 )
  31.   IS
  32.   BEGIN
  33.     payload := dbms_lob.substr(self.m_payload,4000,1);
  34.   END;
  35.  
  36.   MEMBER PROCEDURE get_text ( payload OUT CLOB )
  37.   IS
  38.   BEGIN
  39.     payload := self.m_payload;
  40.   END;
  41.  
  42.   MEMBER PROCEDURE set_string_property (
  43.                 property_name   IN      VARCHAR,
  44.                 property_value  IN      VARCHAR )
  45.   IS
  46.     l_newXML CLOB := '';
  47.     l_name varchar2(2048);
  48.     l_value varchar2(2048);
  49.     l_result_cnt number;
  50.     l_tochange boolean := true;
  51.   BEGIN
  52.  
  53.   l_newXML := '<a>';
  54.  l_result_cnt := 1;
  55.   WHILE self.m_properties.EXISTSNODE('/a/p[' || l_result_cnt || ']') = 1
  56.   LOOP
  57.     SELECT
  58.       EXTRACTVALUE(self.m_properties, '/a/p[' || l_result_cnt || ']/n'),
  59.       EXTRACTVALUE(self.m_properties, '/a/p[' || l_result_cnt || ']/v')
  60.     INTO
  61.       l_name,
  62.       l_value
  63.     FROM dual;
  64.    
  65.     if l_name=property_name then
  66.       l_value := property_value;
  67.       l_tochange := false;
  68.     end if;
  69.    
  70.     dbms_lob.append(l_newXML,'<p><n>' || l_name || '</n><v>' || l_value || '</v></p>');
  71.    
  72.     l_result_cnt := l_result_cnt + 1;
  73.   END LOOP;  
  74.   if l_tochange then
  75.     dbms_lob.append(l_newXML,'<p><n>' || property_name || '</n><v>' || property_value || '</v></p>');
  76.   end if;
  77.   dbms_lob.append(l_newXML,'</a>');
  78.  
  79.    self.m_properties := XMLTYPE(l_newXML);
  80.   END;
  81.  
  82.   MEMBER FUNCTION get_string_property ( property_name   IN      VARCHAR)
  83.   RETURN   VARCHAR
  84.   IS
  85.     l_value varchar2(2048);
  86.   BEGIN
  87.     SELECT
  88.       EXTRACTVALUE(self.m_properties, '/a/p[./n="' || property_name || '"]/v')
  89.     INTO
  90.       l_value
  91.     FROM dual;
  92.     return l_value;  
  93.   END;
  94.  
  95.  
  96. end;

Tags

RJM Article Type
Quick Reference