1 Reply Latest reply: Sep 22, 2009 7:39 AM by 723764 RSS

    JMS Text Msg Payload - Oracle 8i

    711163
      I am using Oracle8i Enterprise Edition Release 8.1.7.4.0 for creating a queue uisng Oracle AQ with payload type
      SYS.AQ$_JMS_TEXT_MESSAGE. When I tried create a queue, I found some of components in SYS.AQ$_JMS_TEXT_MESSAGE are missing in Oracle 8i. Without those components usage, the message cannot set as payload in the queue. The explanation is as below.

      My script for creation of queue is as follows..

      CREATE OR REPLACE PROCEDURE NCR_ESB_NOL_SCRIPT(order_clob_load in CLOB)
      IS
      qtable_present number;
      queue_present number;
      enqopt dbms_aq.enqueue_options_t;
      msgprop dbms_aq.message_properties_t;
      enq_msgid raw(10000);
      order_headers_clob clob;
      msgsize number(38);
      message sys.aq$_jms_text_message;
      clob_msg_queuetable varchar2(100);
      clob_msg_queue varchar2(100);

      BEGIN

      -- Queue table and queue
      clob_msg_queuetable := 'TEST_QTAB';
      clob_msg_queue := 'TEST_Q';

      order_headers_clob := order_clob_load;

      --create queue table

      select count(*) into qtable_present from user_tables where table_name = 'TEST_QTAB';
           if (qtable_present <> 1)
           then
                dbms_aqadm.create_queue_table(queue_table => clob_msg_queuetable,
                                              multiple_consumers => TRUE,
                                         queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
                                         compatible => '8.1');
           end if;

      --create queue 

      select count(*) into queue_present from user_queues where name = 'TEST_Q';
           if (queue_present <> 1)
           then
                dbms_aqadm.create_queue(queue_name=>clob_msg_queue,
      queue_table => clob_msg_queuetable,
      retention_time => 86400);
      dbms_aqadm.add_subscriber(clob_msg_queue, SYS.AQ$_AGENT ('OA', NULL, NULL));
           end if;
           
      dbms_aqadm.start_queue(clob_msg_queue);

      message := sys.aq$_jms_text_message.construct;

      message.setText(order_headers_clob);

      dbms_aq.enqueue(
      queue_name => clob_msg_queue, -- IN
      enqueue_options => enqopt, -- IN
      message_properties => msgprop, -- IN
      payload => message, -- IN
      msgid => enq_msgid); -- OUT

      COMMIT;


      When I tried the above script, I got error as follows.


      PL/SQL: Statement ignored
      PLS-00302: component 'CONSTRUCT' must be declared
      PL/SQL: Statement ignored
      PLS-00302: component 'SETTEXT' must be declared



      When I tried DESC SYS.AQ$_JMS_TEXT_MESSAGE , it shows as :

      SQL> DESC SYS.AQ$_JMS_TEXT_MESSAGE
      Name Null? Type
      ----------------------------------------- -------- ------------------
      HEADER AQ$_JMS_HEADER
      TEXT_LEN NUMBER(38)
      TEXT_VC VARCHAR2(4000)
      TEXT_LOB CLOB

      It does not show the components CONSTRUCT and SET_TEXT. Also I will be in need of GET_TEXT component also to dequeue the queue data and test it. I would like to know whether the components CONSTRUCT and SET_TEXT are availble in Oracle 8i or not. Without those components, is there any other way to set payload in the queue?

      Help in this regard from any body who knows about this is really appreciated.

      Thanks
        • 1. Re: JMS Text Msg Payload - Oracle 8i
          723764
          Hi, not sure where you have sorted this out yet, but I ended up creating my own wrappers. Hope these are of use:

          FUNCTION new_properties
          RETURN SYS.AQ$_JMS_USERPROPARRAY
          IS
          l_prop_arr SYS.AQ$_JMS_USERPROPARRAY;
          BEGIN
          l_prop_arr := SYS.AQ$_JMS_USERPROPARRAY();

          RETURN l_prop_arr;
          END new_properties;
          ----------------------------------------------------------------------------------------------------
          ----------------------------------------------------------------------------------------------------
          FUNCTION new_agent
          RETURN SYS.AQ$_AGENT
          IS
          c_name CONSTANT VARCHAR2(30) := NULL;
          c_address CONSTANT VARCHAR2(100) := NULL;
          c_protocol CONSTANT NUMBER := 0;

          l_agent SYS.AQ$_AGENT;
          BEGIN
          l_agent := SYS.AQ$_AGENT(c_name, c_address, c_protocol);

          RETURN l_agent;
          END new_agent;
          ----------------------------------------------------------------------------------------------------
          ----------------------------------------------------------------------------------------------------
          FUNCTION new_header
          RETURN SYS.AQ$_JMS_HEADER
          IS
          c_type CONSTANT VARCHAR2(100) := NULL;
          c_jms_userid CONSTANT VARCHAR2(100) := 'waveq';
          c_appid CONSTANT VARCHAR2(100) := NULL;
          c_groupid CONSTANT VARCHAR2(100) := NULL;
          c_groupseq CONSTANT INTEGER := 1;

          l_replyto SYS.AQ$_AGENT;
          l_proparr SYS.AQ$_JMS_USERPROPARRAY;
          l_jms_header SYS.AQ$_JMS_HEADER;
          BEGIN
          l_replyto := new_agent();
          l_proparr := new_properties();

          l_jms_header := SYS.AQ$_JMS_HEADER(l_replyto, c_type, c_jms_userid, c_appid, c_groupid, c_groupseq, l_proparr);

          RETURN l_jms_header;
          END new_header;
          ----------------------------------------------------------------------------------------------------
          ----------------------------------------------------------------------------------------------------
          FUNCTION new_payload(
          io_message IN OUT NOCOPY VARCHAR2)
          RETURN SYS.AQ$_JMS_TEXT_MESSAGE
          IS
          c_text_len CONSTANT INTEGER := 0;
          c_text_vc CONSTANT VARCHAR2(4000) := NULL;
          c_text_lob CONSTANT CLOB := EMPTY_CLOB();

          l_header SYS.AQ$_JMS_HEADER;
          l_jms_message SYS.AQ$_JMS_TEXT_MESSAGE;
          BEGIN
          l_header := new_header();

          l_jms_message := SYS.AQ$_JMS_TEXT_MESSAGE(l_header, c_text_len, c_text_vc, c_text_lob);

          l_jms_message.text_vc := io_message;
          l_jms_message.text_len := LENGTH(io_message);

          RETURN l_jms_message;
          END new_payload;