Home » RDBMS Server » Performance Tuning » Please help on this (Oracle 11g)
Please help on this [message #592293] Tue, 06 August 2013 06:37 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi All,

I have stuck up in implementing following 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.

I am posting required scripts and test cases.

CREATE TABLE LEAD_TIME_TABLE(sku VARCHAR2(20),model VARCHAR2(20),class VARCHAR2(20),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');


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.

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


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.

SELECT * FROM LEAD_TIME_TABLE_STAGING;
BUSINESS_UNIT_ID       SKU        GEO   LEAD_TIME
2828                   A0814822    BE     30

For requirement1 I have created the following package.

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 UpdateData(p_leadtime_nodes XmlType)
    is
    begin
        if (p_leadtime_nodes is not null) then
           UPDATE ltc_global.LEAD_TIME_TABLE old SET lead_time=
            (
                select LEAD_TIME
                  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
               WHERE new.SKU=old.sku
                 AND new.geo=old.geo
            )
       
         WHERE EXISTS
         (
             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
               WHERE new.SKU=old.sku
                 AND new.geo=old.geo
            );
        end if;
       
         exception
        when others then
            LogDataError(
                'UpdateData Procedure Failed #: '||chr(10)||sqlerrm
              , null
              , null
            );
    END UpdateData;
END messg_values_process;
/


some of the records are not updating in the table.

Test case:

<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" />
   <z:row business_unit_id="2828" geo="BE" sku="A0814823" dw_maint_flag="False" stock_status_id="0" lead_time="40" 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>
 
CREATE TABLE LEAD_TIME_TABLE(sku VARCHAR2(20),model VARCHAR2(20),class CHAR(1),geo VARCHAR2(6),lead_time NUMBER,created DATE);
 
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814818','AT',20);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814818','AB',10);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814820','BE',20);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814819','GB',20);
INSERT INTO LEAD_TIME_TABLE(SKU,GEO,LEAD_TIME) VALUES('A0814823','BE',20);
 
Why the following two records were not update.
Even satisfying this condition WHERE LEAD_TIME_TABLE.SKU=sku from xml message AND LEAD_TIME_TABLE.GEO=geo from xml message;
'A0814820','BE',20
'A0814823','BE',20
 
After updating the output I got as below.
VK_UNIT   MODEL   CLASS  GEO  LEAD_TIME  CREATED
---------- --------- --- -------------------- --------------------------------------------
A0814818                              AT      30                                                    
A0814818                              AB      10                                                    
A0814820                              BE      20                                                    
A0814819                              GB      30                                                    
A0814823                              BE                                                                            
 
5 rows selected.


Please help me.

Your earliest response is appreciated.
Thanks in advance.
Re: Please help on this [message #592314 is a reply to message #592293] Tue, 06 August 2013 08:23 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member

Can you please help me.

Thanks

[Updated on: Sun, 02 March 2014 13:12] by Moderator

Report message to a moderator

Re: Please help on this [message #592317 is a reply to message #592314] Tue, 06 August 2013 08:48 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
What are you doing while you await this free help?
Re: Please help on this [message #592321 is a reply to message #592317] Tue, 06 August 2013 09:19 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I have tried but it's not giving expected result.

Thanks.
Re: Please help on this [message #594562 is a reply to message #592321] Fri, 30 August 2013 02:54 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
You have requested that numerous posts be deleted from this thread, but you have not supplied a valid reason for your requests. This forum does not remove posts just because another poster requests it. Please tell the moderators WHY you want those posts removed.
Previous Topic: Impact of RAM to oracle
Next Topic: Performance issue with a query using DB links
Goto Forum:
  


Current Time: Thu Mar 28 07:37:01 CDT 2024