8 Replies Latest reply on Jul 22, 2013 9:59 AM by 976208

    Please help me in inserting data into table from XML message.

    976208

      Hi Experts,

       

       

      Please provide a procedure to implement my below requirement.

      Since I am new to queue concept.

       

       

      [code]- <MESSAGES>

      - <MESSAGE ID="3026900">

        <MSG_ID>3026900</MSG_ID>

        <DT_POSTED>6/20/2013 08:15:48</DT_POSTED>

        <POSTED_BY>GPD_MSG_EXTRACTOR</POSTED_BY>

        <DT_LAST_QUEUED />

      - <MSG>

      - <WORK_SET TRANSACTION_ID="@TRANS_ID" TRANSACTION_TYPE="Batch" IS_ACID="@IS_ACID">

      - <WORK_UNIT GROUP="LEAD_TIME" ACTION="UPDATE" AFFECTED="LEAD_TIME">

      - <RECORDSET TABLE_NAME="LEAD_TIME">

      - <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">

      - <rs:data>

        <z:row business_unit_id="3838" geo="AT" sku="A0814818" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />

        <z:row business_unit_id="202" geo="GB" sku="A0814819" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />

        <z:row business_unit_id="2828" geo="BE" sku="A0814820" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />

        </rs:data>

        </xml>

        </RECORDSET>

        </WORK_UNIT>

      - <WORK_UNIT GROUP="LEAD_TIME" ACTION="ADD" AFFECTED="LEAD_TIME">

      - <RECORDSET TABLE_NAME="LEAD_TIME">

      - <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">

      - <rs:data>

        <z:row business_unit_id="202" geo="GB" sku="A0814821" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />

        <z:row business_unit_id="2828" geo="BE" sku="A0814822" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />

        </rs:data>

        </xml>

        </RECORDSET>

        </WORK_UNIT>

      - <WORK_UNIT GROUP="LEAD_TIME" ACTION="DELETE" AFFECTED="LEAD_TIME">

      - <RECORDSET TABLE_NAME="LEAD_TIME">

      - <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">

      - <rs:data>

        <z:row business_unit_id="202" geo="GB" sku="A0814817" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />

        <z:row business_unit_id="2828" geo="BE" sku="A0814816" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />

        </rs:data>

        </xml>

        </RECORDSET>

        </WORK_UNIT>

        </WORK_SET>

        </MSG>

        </MESSAGE>

        </MESSAGES>[/code]

       

       

      The above XML message is receieved by our oracle queue.Once the message is receieved to our queue.

      We have to process the message according to the action(UPDATE ADD DELETE).

      From this message we have to process only 3 fields (geo  sku lead_time).

      These three feilds data should be processed into the SKU_LEAD_TIME table based on the action.

       

       

      Req1:

      IF the action is update then the records should be updated in the "LEAD_TIME_TABLE" table

       

       

      IF the action is update then the records should be updated in the "LEAD_TIME_TABLE" table

      based on the below condition.

       

       

      [code]UPDATE LEAD_TIME_TABLE SET lead_time= lead time from xml message

      WHERE LEAD_TIME_TABLE.SKU=sku from xml message AND LEAD_TIME_TABLE.GEO=geo from xml message;[/code]

       

       

      Req2:

      IF the action is delete then the records should be deleted from the "LEAD_TIME_TABLE" table.

      based on the below condition.

       

       

      [code]DELETE FROM LEAD_TIME_TABLE

      WHERE LEAD_TIME_TABLE.SKU=sku from xml message AND LEAD_TIME_TABLE.GEO=geo from xml message;[/code]

       

       

      Req3:

      IF the action is add then the records should be inserted in the "LEAD_TIME_TABLE"  and "LEAD_TIME_TABLE_STAGING" table

      based on the below conditions.

       

       

      Req3.1:

      If SKU from XML message is available in "SKU_TABLE" table

      then the three parameter(GEO SKU LEAD_TIME) values from XML message should be inserted into "LEAD_TIME_TABLE" table.

       

       

      Req3.2:

      If SKU from XML message is not available in "SKU_TABLE" table

      then the three parameter(GEO SKU LEAD_TIME) values from XML message should be inserted into "LEAD_TIME_TABLE_STAGING" table.

       

       

      Req3.3:

      Once the new SKU is avilable in "SKU_TABLE" table then the corresponding SKU record should be

      inserted into "LEAD_TIME_TABLE" table and that record should be deleted from  "LEAD_TIME_TABLE_STAGING" table.

       

       

      I am posting required scripts and test cases.

       

       

      [code]CREATE TABLE LEAD_TIME_TABLE(sku VARCHAR2(20),model VARCHAR2(20),class CHAR(1),geo VARCHAR2(6),lead_time NUMBER,created DATE);

       

       

      CREATE TABLE LEAD_TIME_TABLE_STAGING(sku VARCHAR2(20),geo VARCHAR2(6),lead_time NUMBER);

       

       

      CREATE TABLE SKU_TABLE(sku VARCHAR2(20),geo VARCHAR2(6),lead_time NUMBER);

       

       

       

       

      INSERT INTO SKU_TABLE VALUES('A0814817','GB',30);

      INSERT INTO SKU_TABLE VALUES('A0814818','AT',30);

      INSERT INTO SKU_TABLE VALUES('A0814819','GB',30);

      INSERT INTO SKU_TABLE VALUES('A0814820','BE',30);

      INSERT INTO SKU_TABLE VALUES('A0814821','GB',30);

       

       

      INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814817','GB',30);

      INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814818','AT',20);

      INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814819','GB',20);

      INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814820','BE',20);

       

       

      INSERT INTO LEAD_TIME_TABLE_STAGING VALUES('A0814822','BE',30);[/code]

       

       

       

       

      From the above message.

       

       

      Test case for req3.1:

      The action for SKU numbers "A0814821"  "A0814822" are insert.

      The SKU number  "A0814821" is available in SKU_TABLE table.

      so the record should be inserted in the LEAD_TIME_TABLE.

       

       

      [code]SELECT * FROM LEAD_TIME_TABLE;

       

       

      SKU        GEO   LEAD_TIME

      A0814817    GB    30

      A0814818    AT    20

      A0814819    GB    20

      A0814820    BE    20

      A0814821    GB    30[/code]

       

       

      Test case for req3.2:

      The action for SKU numbers "A0814821"  "A0814822" are insert.

      The SKU number  "A0814822" is not available in SKU_TABLE table.

      so the record should be inserted in the LEAD_TIME_TABLE_STAGING.

       

       

      [code]SELECT * FROM LEAD_TIME_TABLE_STAGING;

       

       

      SKU        GEO   LEAD_TIME

       

       

      A0814822    BE     30[/code]

       

       

      Test case for req3.3:

       

       

      The action for SKU numbers "A0814821"  "A0814822" are insert.

      The SKU number  "A0814822" is not available in SKU_TABLE table.

      Once I insert SKU number "A0814822" in SKU_TABLE table.

      The corresponding records automatically should be inserted in the LEAD_TIME_TABLE

      and should be deleted from LEAD_TIME_TABLE_STAGING.

       

       

      [code]SELECT * FROM LEAD_TIME_TABLE_STAGING;  --- should return NULL.

       

       

      SELECT * FROM LEAD_TIME_TABLE;

       

       

      SKU        GEO   LEAD_TIME

      A0814817    GB    30

      A0814818    AT    20

      A0814819    GB    20

      A0814820    BE    20

      A0814821    GB    30

      A0814822    BE    30[/code]

       

       

      Please help me.

       

       

      Thanks in advance.

        • 1. Re: Please help me in inserting data into table from XML message.
          BluShadow

          So, the first thing you need is to extract the data from the XML... something like...

           

          SQL> ed
          Wrote file afiedt.buf

            1  with t as (select xmltype('<MESSAGES>
            2    <MESSAGE ID="3026900">
            3      <MSG_ID>3026900</MSG_ID>
            4      <DT_POSTED>6/20/2013 08:15:48</DT_POSTED>
            5      <POSTED_BY>GPD_MSG_EXTRACTOR</POSTED_BY>
            6      <DT_LAST_QUEUED />
            7      <MSG>
            8        <WORK_SET TRANSACTION_ID="@TRANS_ID" TRANSACTION_TYPE="Batch" IS_ACID="@IS_ACID">
            9          <WORK_UNIT GROUP="LEAD_TIME" ACTION="UPDATE" AFFECTED="LEAD_TIME">
          10            <RECORDSET TABLE_NAME="LEAD_TIME">
          11              <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
          12                <rs:data>
          13                  <z:row business_unit_id="3838" geo="AT" sku="A0814818" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
          14                  <z:row business_unit_id="202" geo="GB" sku="A0814819" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
          15                  <z:row business_unit_id="2828" geo="BE" sku="A0814820" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
          16                </rs:data>
          17              </xml>
          18            </RECORDSET>
          19          </WORK_UNIT>
          20          <WORK_UNIT GROUP="LEAD_TIME" ACTION="ADD" AFFECTED="LEAD_TIME">
          21            <RECORDSET TABLE_NAME="LEAD_TIME">
          22              <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
          23                <rs:data>
          24                  <z:row business_unit_id="202" geo="GB" sku="A0814821" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
          25                  <z:row business_unit_id="2828" geo="BE" sku="A0814822" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
          26                </rs:data>
          27              </xml>
          28            </RECORDSET>
          29          </WORK_UNIT>
          30          <WORK_UNIT GROUP="LEAD_TIME" ACTION="DELETE" AFFECTED="LEAD_TIME">
          31            <RECORDSET TABLE_NAME="LEAD_TIME">
          32              <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
          33                <rs:data>
          34                  <z:row business_unit_id="202" geo="GB" sku="A0814817" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
          35                  <z:row business_unit_id="2828" geo="BE" sku="A0814816" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />
          36                </rs:data>
          37              </xml>
          38            </RECORDSET>
          39          </WORK_UNIT>
          40        </WORK_SET>
          41      </MSG>
          42    </MESSAGE>
          43  </MESSAGES>') as xml from dual)
          44  --
          45  -- end of sample data
          46  --
          47  select x.grp, x.action, x.affect
          48        ,y.geo, y.sku, y.lead_time
          49  from   t
          50        ,xmltable(xmlnamespaces('urn:schemas-microsoft-com:rowset' as "rs", '#RowsetSchema' as "z"),
          51                  '/MESSAGES/MESSAGE/MSG/WORK_SET/WORK_UNIT'
          52                  passing t.xml
          53                  columns grp    varchar2(20) path './@GROUP'
          54                         ,action varchar2(10) path './@ACTION'
          55                         ,affect varchar2(20) path './@AFFECTED'
          56                         ,data   xmltype      path './RECORDSET/xml/rs:data'
          57                 ) x
          58        ,xmltable(xmlnamespaces('urn:schemas-microsoft-com:rowset' as "rs", '#RowsetSchema' as "z"),
          59                  '/rs:data/z:row'
          60                  passing x.data
          61                  columns geo       varchar2(2)  path './@geo'
          62                         ,sku       varchar2(10) path './@sku'
          63                         ,lead_time number       path './@lead_time'
          64*                ) y
          SQL> /

           

          GRP                  ACTION     AFFECT               GE SKU         LEAD_TIME
          -------------------- ---------- -------------------- -- ---------- ----------
          LEAD_TIME            UPDATE     LEAD_TIME            AT A0814818           30
          LEAD_TIME            UPDATE     LEAD_TIME            GB A0814819           30
          LEAD_TIME            UPDATE     LEAD_TIME            BE A0814820           30
          LEAD_TIME            ADD        LEAD_TIME            GB A0814821           30
          LEAD_TIME            ADD        LEAD_TIME            BE A0814822           30
          LEAD_TIME            DELETE     LEAD_TIME            GB A0814817           30
          LEAD_TIME            DELETE     LEAD_TIME            BE A0814816           30

           

          7 rows selected.

           

          After that I'm sure you can do the inserts, updates etc. that you need on the tables.

          • 2. Re: Please help me in inserting data into table from XML message.
            976208

            Thanks for your help.

            However I am not able to complete the code.

            This message in queue.

            So that first we have to get the message from queue and then extract the data from the XML.

            Could you please provide complete code (procedure) for my requirement.


            Thanks in advance.

            • 3. Re: Please help me in inserting data into table from XML message.
              odie_63

              Could you please provide complete code (procedure) for my requirement.

              Will we get paid for doing your own job in your place ?


              BluShadow has given you the query to parse the XML message. It was the hardest part of this requirement (relatively speaking).

              For the rest you just have to loop through the generated records and act upon their nature, according to the rules.


              If you want help, at least show that you've tried something, and explain where you have issues.


              • 4. Re: Please help me in inserting data into table from XML message.
                976208

                Hi All,

                 

                 

                I have created the following queue ,payload and queue table

                using the following script.

                [code]

                BEGIN

                  SYS.DBMS_AQADM.CREATE_QUEUE

                  (

                    QUEUE_NAME          =>   'APPS_GLOBAL.HARMONY_GII_QUEUE'

                   ,QUEUE_TABLE         =>   'APPS_GLOBAL.HARMONY_GII_TABLE'

                   ,QUEUE_TYPE          =>   SYS.DBMS_AQADM.NORMAL_QUEUE

                   ,MAX_RETRIES         =>   1000

                   ,RETRY_DELAY         =>   0

                   ,RETENTION_TIME      =>   0

                   );

                END;

                /

                 

                 

                BEGIN

                  SYS.DBMS_AQADM.START_QUEUE

                  (

                    QUEUE_NAME => 'APPS_GLOBAL.HARMONY_GII_QUEUE'

                   ,ENQUEUE => TRUE

                   ,DEQUEUE => TRUE

                   );

                END;

                /

                 

                 

                 

                 

                BEGIN

                  SYS.DBMS_AQADM.CREATE_QUEUE_TABLE

                  (

                    QUEUE_TABLE           =>        'APPS_GLOBAL.HARMONY_GII_TABLE'

                   ,QUEUE_PAYLOAD_TYPE    =>        'SYS.MGW_BASIC_MSG_T'

                   ,COMPATIBLE            =>        '10.0.0'

                   ,STORAGE_CLAUSE        =>        '

                                                     TABLESPACE XXDELL_TS_QUEUES

                                                     PCTUSED    0

                                                     PCTFREE    10

                                                     INITRANS   11

                                                     MAXTRANS   255

                                                     STORAGE    (

                                                                 INITIAL          64K

                                                                 NEXT             1M

                                                                 MINEXTENTS       1

                                                                 MAXEXTENTS       UNLIMITED

                                                                 PCTINCREASE      0

                                                                 BUFFER_POOL      DEFAULT

                                                                )'

                   ,SORT_LIST             =>        'ENQ_TIME'

                   ,MULTIPLE_CONSUMERS    =>         FALSE

                   ,MESSAGE_GROUPING      =>         0

                   ,PRIMARY_INSTANCE      =>         3

                   ,SECONDARY_INSTANCE    =>         1

                   ,COMMENT               =>         'The Godfather strikes again!'

                   ,SECURE                =>         FALSE

                   );

                End;

                /[/code]

                 

                 

                Now I am trying to write message to queue.using the following code.

                I am getting the error

                [code]ORA-06550: line 9, column 20:

                PLS-00306: wrong number or types of arguments in call to 'MGW_BASIC_MSG_T'

                 

                 

                DECLARE

                       r_enqueue_options    DBMS_AQ.ENQUEUE_OPTIONS_T;

                       r_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;

                       v_message_handle     RAW(16);

                       o_payload            SYS.MGW_BASIC_MSG_T;

                   

                    BEGIN

                      o_payload := SYS.MGW_BASIC_MSG_T('<MESSAGES>

                <MESSAGE ID="3026900">

                  <MSG_ID>3026900</MSG_ID>

                  <DT_POSTED>6/20/2013 08:15:48</DT_POSTED>

                  <POSTED_BY>GPD_MSG_EXTRACTOR</POSTED_BY>

                  <DT_LAST_QUEUED />

                <MSG>

                <WORK_SET TRANSACTION_ID="@TRANS_ID" TRANSACTION_TYPE="Batch" IS_ACID="@IS_ACID">

                <WORK_UNIT GROUP="LEAD_TIME" ACTION="ADD" AFFECTED="LEAD_TIME">

                <RECORDSET TABLE_NAME="LEAD_TIME">

                <xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">

                <rs:data>

                  <z:row business_unit_id="202" geo="GB" sku="A0814821" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />

                  <z:row business_unit_id="2828" geo="BE" sku="A0814822" dw_maint_flag="False" stock_status_id="0" lead_time="30" est_qty="0" leadtime_lock="False" update_dts="2013-06-12T02:08:30.367" update_uid="DEACTIVATE_SKU_PROCESS" leadtime_dts="2010-05-22T02:21:21.707" leadtime_uid="Lead_Time_Rules" />

                  </rs:data>

                  </xml>

                  </RECORDSET>

                  </WORK_UNIT>

                  </WORK_UNIT>

                  </WORK_SET>

                  </MSG>

                  </MESSAGE>

                  </MESSAGES>');

                  

                      DBMS_AQ.ENQUEUE(

                         queue_name         => 'HARMONY_GII_QUEUE',

                         enqueue_options    => r_enqueue_options,

                         message_properties => r_message_properties,

                         payload            => o_payload,

                         msgid              => v_message_handle

                         );

                        COMMIT;

                   END;

                   /[/code]

                Please help me.

                 

                 

                Thanks in advance.

                • 5. Re: Please help me in inserting data into table from XML message.
                  976208

                  I am sending the structure of mgw_basic_msg_t.

                   

                   

                  CREATE OR REPLACE TYPE SYS.mgw_basic_msg_t

                                                         as object

                  (

                    -- message header

                    header       sys.mgw_name_value_array_t,

                   

                   

                    -- message body; typically only one of the below is non-NULL

                    text_body    sys.mgw_text_value_t,

                    raw_body     sys.mgw_raw_value_t,

                   

                   

                    -- === Methods ===

                   

                   

                    STATIC FUNCTION construct

                    RETURN sys.mgw_basic_msg_t

                  );

                  /

                   

                   

                  CREATE OR REPLACE TYPE BODY SYS.mgw_basic_msg_t wrapped

                  a000000

                  1

                  abcd

                  abcd

                  abcd

                  abcd

                  abcd

                  abcd

                  abcd

                  abcd

                  abcd

                  abcd

                  abcd

                  abcd

                  abcd

                  abcd

                  abcd

                  e

                  c2 d2

                  4kzS7Ob7bQmTAmfNoSeeRrqdLeowg5n0dLhcuJu/9MOFLpbQrvJW/1lKcmLc0JbMuHQrjwYJ

                  ad2sqVdX23/b65OvYndyrdvrmq6vMZqWVxQWd602Fh9dq9OrXbAvdojYwR4cHLtdBIopdrv9

                  ihwo8dJ0OdPc4rlBP8oLjxizjifOApLOfRbKWi72yMDqJB9GLmfzyprs+6bft5ZS

                  /

                   

                   

                   

                   

                  Please help me.

                  Thanks.

                  • 6. Re: Please help me in inserting data into table from XML message.
                    976208

                    Hi ,

                     

                    Anybody could you please help me.

                     

                    Thanks.

                    • 7. Re: Please help me in inserting data into table from XML message.
                      BluShadow

                      The clue is in the error message:

                       

                       

                      PLS-00306: wrong number or types of arguments in call to 'MGW_BASIC_MSG_T'

                       

                       

                      CREATE OR REPLACE TYPE SYS.mgw_basic_msg_t as object
                        (
                        -- message header
                        header       sys.mgw_name_value_array_t,

                        -- message body; typically only one of the below is non-NULL
                        text_body    sys.mgw_text_value_t,
                        raw_body     sys.mgw_raw_value_t,

                        -- === Methods ===
                        STATIC FUNCTION construct
                        RETURN sys.mgw_basic_msg_t
                        );
                      /

                       

                      The arguments/attributes it's expecting for that object are 3 things of various types.  You're providing a single argument of string type.... that doesn't match the definition.

                      Provide the correct number of arguments of the correct datatypes.

                      • 8. Re: Please help me in inserting data into table from XML message.
                        976208

                        Hi,

                         

                         

                        I have resolved this issue.

                         

                         

                        But I have stuck up in implementing some of the scenarios.

                         

                         

                        Req1:

                        IF the action is update then the records should be updated in the "LEAD_TIME_TABLE" table

                         

                         

                        IF the action is update then the records should be updated in the "LEAD_TIME_TABLE" table

                        based on the below condition.

                         

                         

                        UPDATE LEAD_TIME_TABLE SET lead_time= lead time from xml message

                        WHERE LEAD_TIME_TABLE.SKU=sku from xml message AND LEAD_TIME_TABLE.GEO=geo from xml message;

                         

                         

                        Req3:

                        IF the action is add then the records should be inserted in the "LEAD_TIME_TABLE"  and "LEAD_TIME_TABLE_STAGING" table

                        based on the below conditions.

                         

                         

                        Req3.1:

                        If SKU from XML message is available in "schema_name.SKU_TABLE" table.

                        The schema_name.SKU_TABLE table should be selected based on "business_unit_id" of XML message and "sid" of MAPPING table WHERE business_unit_id=sid.

                        then the three parameter(GEO SKU LEAD_TIME) values from XML message and two fields MODEL,CLASS from SKU_TABLE

                        should be inserted into "LEAD_TIME_TABLE" table.

                         

                         

                        Req3.2:

                        If SKU from XML message is not available in "schema_name.SKU_TABLE" table

                        The schema_name.SKU_TABLE table should be selected based on "business_unit_id" of XML message and "sid" of MAPPING table WHERE business_unit_id=sid.

                        then the four parameter(BUSINESS_UNIT_ID GEO SKU LEAD_TIME) values from XML message

                        should be inserted into "LEAD_TIME_TABLE_STAGING" table.

                         

                         

                        Req3.3:

                        Once the new SKU is avilable in "schema_name.SKU_TABLE" table then the corresponding SKU record should be

                        inserted into "LEAD_TIME_TABLE" table and that record should be deleted from  "LEAD_TIME_TABLE_STAGING" table.

                        The schema_name.SKU_TABLE table should be selected based on "business_unit_id" of LEAD_TIME_TABLE_STAGING and "sid" of MAPPING table WHERE business_unit_id=sid.

                         

                         

                        I am posting required scripts and test cases.

                         

                         

                        [code]CREATE TABLE LEAD_TIME_TABLE(sku VARCHAR2(20),model VARCHAR2(20),class CHAR(1),geo VARCHAR2(6),lead_time NUMBER,created DATE);

                         

                         

                        CREATE TABLE LEAD_TIME_TABLE_STAGING(business_unit_id NUMBER,sku VARCHAR2(20),geo VARCHAR2(6),lead_time NUMBER);

                         

                         

                        CREATE TABLE SKU_TABLE(sku VARCHAR2(20),model VARCHAR2(20),class VARCHAR2(10));

                         

                         

                        CREATE TABLE mapping(sid NUMBER,schema_name VARCHAR2(20));

                         

                         

                        INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814817','GB',30);

                        INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814818','AT',20);

                        INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814819','GB',20);

                        INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814820','BE',20);

                         

                         

                        INSERT INTO SKU_TABLE VALUES('A0814817','M125','C111');

                        INSERT INTO SKU_TABLE VALUES('A0814818','M127','A111');

                        INSERT INTO SKU_TABLE VALUES('A0814819','M126','A111');

                        INSERT INTO SKU_TABLE VALUES('A0814820','N100','B111');

                        INSERT INTO SKU_TABLE VALUES('A0814821','P123','B111');

                         

                         

                         

                         

                        INSERT INTO mapping VALUES(202,'WEDB');

                        INSERT INTO mapping VALUES(2828,'HR');

                        INSERT INTO mapping VALUES(3838,'BB');

                        [/code]

                         

                         

                        Test case for req3.1:

                        The action for SKU numbers "A0814821"  "A0814822" are insert.

                        The SKU number  "A0814821" is available in schema_name.SKU_TABLE table.

                        so the record should be inserted in the LEAD_TIME_TABLE.

                         

                         

                        [code]SELECT * FROM LEAD_TIME_TABLE;

                         

                         

                        SKU        MODEL  CLASS  GEO   LEAD_TIME

                        A0814817    M125   C111   GB     30

                        A0814818    M127   A111   AT     20

                        A0814819    M126   A111   GB     20

                        A0814820    N100   B111   BE     20

                        A0814821    P123   B111   GB     30

                        [/code]

                        Test case for req3.2:

                        The action for SKU numbers "A0814821"  "A0814822" are insert.

                        The SKU number  "A0814822" is not available in SKU_TABLE table.

                        so the record should be inserted in the LEAD_TIME_TABLE_STAGING.

                         

                         

                        [code]SELECT * FROM LEAD_TIME_TABLE_STAGING;

                         

                         

                        BUSINESS_UNIT_ID       SKU        GEO   LEAD_TIME

                         

                         

                        2828               A0814822    BE     30[/code]

                         

                         

                        Test case3.3.3:

                         

                         

                        The action for SKU numbers "A0814821"  "A0814822" are insert.

                        The SKU number  "A0814822" is not available in SKU_TABLE table.

                        Once I insert SKU number "A0814822" in SKU_TABLE table.

                        The corresponding records automatically should be inserted in the LEAD_TIME_TABLE

                        and should be deleted from LEAD_TIME_TABLE_STAGING.

                         

                         

                        [code]INSERT INTO SKU_TABLE VALUES('A0814822','P123','B111');

                         

                         

                        SELECT * FROM LEAD_TIME_TABLE_STAGING;  --- should return NULL.

                         

                         

                        SELECT * FROM LEAD_TIME_TABLE;

                         

                         

                        SKU        MODEL  CLASS  GEO   LEAD_TIME

                        A0814817    M125   C111   GB     30

                        A0814818    M127   A111   AT     20

                        A0814819    M126   A111   GB     20

                        A0814820    N100   B111   BE     20

                        A0814821    P123   B111   GB     30

                        A0814822    P123   B111   GB     30[/code]

                         

                         

                        I am able to implement Req1 and Req3.1 for action UPDATE and INSERT in single merge statement.

                        However my code is not taking care of all my requirements.

                        Please find my below code.

                         

                         

                        [code]   CREATE OR REPLACE PACKAGE BODY messg_values_process

                           AS

                            procedure ProcessInboundLtimeMessage(p_xml_message XmlType)

                            is

                            begin

                                for work_unit in (

                                    select message.MESSAGE_ID

                                         , work_unit.ACTION

                                         , work_unit.LEADTIME_NODES

                                      from XmlTable(

                                            '/MESSAGES/MESSAGE'

                                            passing p_xml_message

                                            columns

                                                MESSAGE_NO          for ordinality

                                              , MESSAGE_ID          number path '@ID'

                                              , WORK_UNIT_NODES     XmlType path 'MSG/WORK_SET/WORK_UNIT'

                                           ) message

                                         , XmlTable(

                                            XmlNamespaces(

                                                'urn:schemas-microsoft-com:rowset' as "rs"

                                              , '#RowsetSchema' as "z"

                                            ),

                                            '/WORK_UNIT'

                                            passing message.WORK_UNIT_NODES

                                            columns

                                                WORK_UNIT_NO        for ordinality

                                              , ACTION              varchar2(10 char) path '@ACTION'

                                              , LEADTIME_NODES       XmlType path 'RECORDSET[@TABLE_NAME="LEAD_TIME"]/xml/rs:data/z:row'

                                           ) work_unit

                                )

                            LOOP

                                    CASE work_unit.ACTION

                                      WHEN 'ADD'    THEN MergeData(work_unit.LEADTIME_NODES);

                                      WHEN 'UPDATE' THEN MergeData(work_unit.LEADTIME_NODES);

                                      WHEN 'DELETE' THEN RemoveData(work_unit.LEADTIME_NODES);

                                      ELSE null;

                                    END CASE;

                                END LOOP;

                            END ProcessInboundLtimeMessage;

                         

                         

                                procedure MergeData(p_leadtime_nodes XmlType)

                            is

                            begin

                                if (p_leadtime_nodes is not null) then

                                    merge into LEAD_TIME_TABLE old

                                    using(

                                        select *

                                          from XmlTable(

                                                XmlNamespaces(

                                                    'urn:schemas-microsoft-com:rowset' as "rs"

                                                  , '#RowsetSchema' as "z"

                                                ),

                                                '/z:row[not(@business_unit_id=following-sibling::z:row/@business_unit_id)]'

                                                passing p_leadtime_nodes

                                                columns

                                                    ITEM_NO             for ordinality

                                                  , BUSINESS_UNIT_ID    number path '@business_unit_id'

                                                  , GEO                 varchar2(20 char) path '@geo'

                                                  , SKU                 varchar2(20 char) path '@sku'

                                                  , LEAD_TIME           number path '@lead_time'

                                               )

                                    ) NEW

                                       ON (old.VK_UNIT = new.SKU

                                      AND old.GEO=new.GEO)

                                    when matched then update

                                       set old.LEAD_TIME = new.LEAD_TIME

                                    when not matched then INSERT

                                         ( old.VK_UNIT

                                         , old.MODEL

                                         , old.CLASS

                                         , old.GEO

                                         , old.LEAD_TIME

                                         , old.COUNTDOWN

                                         , old.LAST_UPDATED_DATE

                                         )

                                         values

                                         ( new.SKU

                                         ,'A123'

                                         ,'A1234'

                                         , new.GEO

                                         , new.LEAD_TIME

                                         , 'N'

                                         , sysdate

                                         )

                                         ;

                                end if;

                                 exception

                                when others then

                                    LogDataError(

                                        'MergeData Procedure Failed #: '||chr(10)||sqlerrm

                                      , null

                                      , null

                                    );

                            END MergeData;

                        END messg_values_process;

                        /[/code]

                         

                        Please help me.

                         

                         

                        Thanks.