1 Reply Latest reply: Nov 23, 2012 2:32 AM by user648708 RSS

    Sending message from oracle to middleware through oracle messaging gateway

    975406
      Hi,

      I am very new to oracle AQ and oracle messaging gateway. I am actually trying to propagate a message from oracle to middleware through oracle messaging gateway.
      I have created the link between oracle messaging gateway and middleware(IBM Websphere).

      1.*Created an object type with a single attribute of type sys.xmltype*.

      create or replace type xpctas_type as object(payload sys.xmltype);

      2.*Created a qtable with payload type as xpctas_type, a queue and started the queue.*begin

      dbms_aqadm.create_queue_table(
      queue_table => 'xpctas_qtab',
      queue_payload_type => 'xpctas_type',
      multiple_consumers => TRUE
      );
      dbms_aqadm.create_queue(queue_name =>'xpctas_q',
      queue_table => 'xpctas_qtab',
      max_retries => 16);
      dbms_aqadm.start_queue('xpctas_q');
      end;

      3.*Created a transformation that converts user defined type xpctas_type to messaging gateway canonical type sys.MGW_BASIC_MSG_T.*
      For this I created a function that converts xpctas_type to messaging gateway canonical type sys.MGW_BASIC_MSG_T.

      CREATE OR REPLACE FUNCTION APPS.order_2_basic(my_order in xpctas_type)
      RETURN sys.mgw_basic_msg_t
      IS
      v_xml XMLTYPE;
      v_text varchar2(4000);
      v_clob CLOB;
      v_basic sys.mgw_basic_msg_t;
      text_body sys.mgw_text_value_t;
      header     sys.mgw_name_value_array_t;

      BEGIN
      v_xml := XMLTYPE.createXML(my_order,NULL,NULL);
      v_basic := sys.mgw_basic_msg_t.construct;
      header := sys.mgw_name_value_array_t(sys.mgw_name_value_t.construct_integer('MGW_MQ_characterSet','1208'),
      sys.mgw_name_value_t.construct_integer('MGW_MQ_priority', '7'));
      IF(LENGTH(v_xml.getstringval())>0 AND LENGTH(v_xml.getstringval())<=4000) THEN
      v_text := v_xml.getStringVal();
      text_body := sys.mgw_text_value_t(v_text,NULL);
      ELSE
      dbms_lob.createtemporary(v_clob,TRUE,dbms_lob.session);
      v_clob:=v_xml.getClobVal();
      text_body := sys.mgw_text_value_t(NULL,v_clob);
      dbms_lob.freetemporary(v_clob);
      END IF;
      v_basic:=sys.mgw_basic_msg_t(header,text_body,NULL);
      RETURN v_basic;
      END order_2_basic;
      /

      begin
      dbms_transform.create_transformation(
      schema => 'apps',
      name => 'order_to_basic',
      from_schema => 'apps',
      from_type => 'xpctas_type',
      to_schema => 'sys',
      to_type => 'mgw_basic_msg_t',
      transformation => 'Apps.order_2_basic(source.user_data)');
      end;

      4.     Registered a foreign queue.

      declare
      v_options sys.mgw_properties;
      gv_mq_queue_name VARCHAR2(32);
      begin
      gv_mq_queue_name := 'MB.O2C.SOFTWARESOLUTION';
      v_options := sys.mgw_properties(
      sys.mgw_property('MQ_openOptions', '2066') );

      dbms_mgwadm.register_foreign_queue(
      name => 'destq', -- MGW foreign queue name
      linkname => 'mqlink', -- name of link to use
      provider_queue => RTRIM(gv_mq_queue_name), -- name of MQSeries queue
      domain => dbms_mgwadm.DOMAIN_QUEUE, -- single consumer queue
      options => v_options );
      end;


      5.     Added a subscriber with transformation.

      begin
      dbms_mgwadm.add_subscriber(
      subscriber_id => 'sub_aq2mq', -- MGW subscriber name
      propagation_type => dbms_mgwadm.outbound_propagation,
      queue_name => 'apps.xpctas_q',
      destination => 'destq@mqlink',
      transformation => 'apps.order_to_basic');
      end;


      6.     Added a scheduler

      begin
      dbms_mgwadm.schedule_propagation(
      -- schedule name
      schedule_id => 'sch_aq2mq',
      -- outbound propagation
      propagation_type => dbms_mgwadm.outbound_propagation,
      -- AQ queue name
      source =>'apps.xpctas_q',
      -- MGW foreign queue with link
      destination =>'destq@mqlink');
      -- The remaining fields currently not used by MGW
      end;

      7.     Enqueued a user defined data type into the qtable.


      declare
      l_xmlstring varchar2(2000);
      l_payload sys.xmltype;
      my_order xpctas_type;
      enqueue_options DBMS_AQ.enqueue_options_t;
      message_properties DBMS_AQ.message_properties_t;
      msgid RAW( 16 );
      v_num Number;
      begin
      SELECT '<?xml version="1.0" encoding="UTF-8" ?>
      <Q1:XXRFG_PRCS_CNCT_TO_ASSETS_STG xmlns:Q1="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/XXRFG_PRCS_CNCT_TO_ASSETS_STG" version="3.0.0" verb="Create" locale="en_US.UTF-8" delta="false">
      <Q1:STAGING_ID>221</Q1:STAGING_ID>
      <Q1:SW_INSTANCE_ID>18595755</Q1:SW_INSTANCE_ID>
      <Q1:MC_INSTANCE_ID>194734</Q1:MC_INSTANCE_ID>
      <Q1:OPCO>NUK</Q1:OPCO>
      <Q1:RELATIONSHIP_FLAG>N</Q1:RELATIONSHIP_FLAG>
      <Q1:RELATIONSHIP_TYPE>Connected To</Q1:RELATIONSHIP_TYPE>
      <Q1:ObjectEventId />
      </Q1:XXRFG_PRCS_CNCT_TO_ASSETS_STG>'
      INTO l_xmlstring
      FROM dual;
      SELECT XMLTYPE(l_xmlstring)
      INTO l_payload
      FROM dual;
      my_order:=xpctas_type(l_payload);
      dbms_output.put_line(my_order.payload.getstringval());
      DBMS_AQ.enqueue( queue_name => 'APPS.xpctas_q',
      enqueue_options => enqueue_options,
      message_properties => message_properties,
      payload => my_order,
      msgid => msgid
      );
      COMMIT;
      END;

      As soon as I enqueued the message the subscriber picked the message and sent it to middleware. I found the below message in the middleware

      MD            ÿÿÿÿ      ¸  MQSTR         AMQ HBU473QC61  PTÀ+H^                                                                            HBU473QC61                                      mqm                                                                                                        2012112109335869                                       ÿÿÿÿ
      *<XPCTAS_TYPE><PAYLOAD><Q1:XXRFG_PRCS_CNCT_TO_ASSETS_STG xmlns:Q1="http://www.ibm.com/websphere/crossworlds/2002/BOSchema/XXRFG_PRCS_CNCT_TO_ASSETS_STG" version="3.0.0" verb="Create" locale="en_US.UTF-8" delta="false">*
      *<Q1:STAGING_ID>221</Q1:STAGING_ID>*
      *<Q1:SW_INSTANCE_ID>18595755</Q1:SW_INSTANCE_ID>*
      *<Q1:MC_INSTANCE_ID>194734</Q1:MC_INSTANCE_ID>*
      *<Q1:OPCO>NUK</Q1:OPCO>*
      *<Q1:RELATIONSHIP_FLAG>N</Q1:RELATIONSHIP_FLAG>*
      *<Q1:RELATIONSHIP_TYPE>Connected To</Q1:RELATIONSHIP_TYPE>*
      *<Q1:ObjectEventId/>*
      *</Q1:XXRFG_PRCS_CNCT_TO_ASSETS_STG>*
      *</PAYLOAD></XPCTAS_TYPE>*

      It has some junk data in the begining. How can I remove this junk data?? Any help provided on this would be of great help.
      Experts on oracle mesaging gateway and AQ, Please help.

      Thanks & Regards,
      Sachin