Oracle nested xml parsing

Question

I have an excel and while import excel with application I convert to it XML string then send it to procedure in Oracle. Code below is my procedure.

PROCEDURE SP_UPLOAD_KALEMS(P_REFCURSOR OUT SYS_REFCURSOR,P_YEAR IN NUMBER,P_MONTH IN NUMBER,P_KALEMS IN CLOB) IS personelId NUMBER; BEGIN
     SAVEPOINT start_tran;
     DELETE FROM HRANALY.WAGE_ACTUAL WA WHERE WA.A_MONTH=P_MONTH AND WA.A_YEAR=P_YEAR;
       FOR r IN (
            select * FROM XMLTABLE('/ArrayOfBaseUpload/BaseUpload'PASSING xmltype(P_KALEMS) 
            COLUMNS  
                SICIL  NUMBER    PATH './SICIL',  
                SAP_ORG_KOD NUMBER     PATH './SAP_ORG_KOD' ,
                POSITION VARCHAR2(100)     PATH './POSITION',
                IS_INFO VARCHAR2(10) PATH './IS',
                FABRIC VARCHAR2(10) PATH './FABRIC',
                COST_CENTER NUMBER     PATH './COST_CENTER' ,
                PERSONE_TYPE VARCHAR2(10) PATH './PERSONE_TYPE',
                UCRET_TIP VARCHAR2(10)     PATH './UCRET_TIP' ,
                BELGE_KOD VARCHAR2(200) PATH './BELGE_KOD',
                TUTAR NUMBER     PATH './TUTAR' ,
                SGK_GUN NUMBER     PATH './SGK_GUN' ,
                SSK_MATRAH NUMBER     PATH './SSK_MATRAH' ,
                SS_MATRAH NUMBER     PATH './SS_MATRAH' ,
                YASAL_NET NUMBER     PATH './YASAL_NET' ,
                ODEME_TUTARI NUMBER     PATH './ODEME_TUTARI' 
                )  PERSONELS
        )LOOP
            personelId:=HRANALY.SEQ_WAGE_MAIN.nextval;
            INSERT INTO  HRANALY.WAGE_ACTUAL(ID,SICIL,SAP_ORG_KOD, POSITION,IS_INFO,FABRIC,COST_CENTER,PERSONE_TYPE,A_MONTH,A_YEAR,UCRET_TIP,BELGE_KOD,
                                             TUTAR,SGK_GUN,SSK_MATRAH,SS_MATRAH,YASAL_NET,ODEME_TUTARI)
            VALUES(personelId,r.SICIL,r.SAP_ORG_KOD,r.POSITION,r.IS_INFO,r.FABRIC,r.COST_CENTER,r.PERSONE_TYPE,P_MONTH,P_YEAR,R.UCRET_TIP,r.BELGE_KOD,
                                             r.TUTAR,r.SGK_GUN,r.SSK_MATRAH,r.SS_MATRAH,r.YASAL_NET,r.ODEME_TUTARI);
            FOR p IN (
                select * FROM XMLTABLE('/ArrayOfBaseUpload/BaseUpload/DETAILS/DetayUpload'PASSING xmltype(P_KALEMS)              
                COLUMNS CODE varchar2(100) PATH './CODE', 
                SICIL NUMBER PATH './../../SICIL',
                AMOUNT NUMBER PATH './AMOUNT') kalems            
            )
            LOOP
                IF r.SICIL=p.SICIL THEN
                    INSERT INTO HRANALY.WAGE_ACTUAL_DETAIL(ID,REF_WAGE,AMOUNT,KALEM_KOD,A_MONTH,A_YEAR)
                    VALUES(HRANALY.SEQ_WAGE_DETAIL.nextval,personelId,p.AMOUNT,p.CODE,P_MONTH,P_YEAR);
                END IF;           
            END LOOP;
        END LOOP;  
        COMMIT;
        OPEN P_REFCURSOR FOR
        SELECT 'SUCCESS' AS RESULT FROM DUAL;  
     EXCEPTION
     WHEN OTHERS THEN
       ROLLBACK TO start_tran;
        OPEN P_REFCURSOR FOR
        SELECT 'ERROR' AS RESULT FROM DUAL;
       RAISE;
    END ;

My problem is that I have about 2000 rows in excell and about 40 columns(it canbe more or less). 15 of them is static columns that save in HRANALY.WAGE_ACTUAL table anothers are dynamic and inserted into HRANALY.WAGE_ACTUAL_DETAIL . SICIL is unique such as identity code for user. For one sicil, multiple details can be inserted to HRANALY.WAGE_ACTUAL_DETAIL My code takes too much time to insert all variables. I want to optimize this code much faster. How can I speed it.

My xml like

<ArrayOfBaseUpload>
    <BaseUpload>
        <SICIL>1</SICIL>
        <SAP_ORG_KOD>500</SAP_ORG_KOD>
        <POSITION>Operator</POSITION>
        <IS>TR - Dikim </IS>
        <FABRIC>IZ01</FABRIC>
        <COST_CENTER>100</COST_CENTER>
        <PERSONE_TYPE>T2</PERSONE_TYPE>
        <UCRET_TIP>Brüt</UCRET_TIP>
        <BELGE_KOD>1</BELGE_KOD>
        <TUTAR>10.00</TUTAR>
        <SGK_GUN>30</SGK_GUN>
        <SSK_MATRAH>100</SSK_MATRAH>
        <SS_MATRAH>100</SS_MATRAH>
        <YASAL_NET>100</YASAL_NET>
        <ODEME_TUTARI>100</ODEME_TUTARI>
        <DETAILS>
            <DetayUpload><CODE>TEMEL_UCRET</CODE><AMOUNT>100</AMOUNT></DetayUpload>//here there can ve 40 data like that
        </DETAILS>
    </BaseUpload>
</ArrayOfBaseUpload>

Thanks in advance

Do you know which part of the procedure is slow, so we know what to focus on? (The fancy way to find out is with PL/SQL profiling. Or you can simply comment out some parts of the procedure and re-run.) For example if only the INSERT statements are slow, a simple way to improve performance is to save the results in a collection and use a FORALL to write multiple rows at a time. If both the reads and the writes are slow, it might make sense to re-write the loops into a single SQL statement. — Jon Heller, Commented Jun 25, 2020 at 7:47
Slowing reason can be nested loop. first loop includes about 2000 data and second one about 60000(if 30 details for each data) then this takes too much time and there is also if condition here to check if same uniqe SICIL . That can be main reason for time @JonHeller — Cagri Tacyildiz, Commented Jun 25, 2020 at 7:52
For performance problems it's important to know the run time for each line of code, instead of just guessing. Without that information, it's easy to get compulsive tuning disorder and optimize things that don't matter. With profiling and information like "line X accounts for Y% of the runtime" we can be sure we're only solving problems that really matter. — Jon Heller, Commented Jun 25, 2020 at 8:06

Chris Saxon · Accepted Answer · 2020-06-25 12:55:34Z

The best way to write slow SQL is to put insert/update/delete inside a loop which changes one row at a time.

If you want fast SQL, have a single statement that changes all the rows. Replacing the loops with two insert-select statements should speed this up:

insert into hranaly.wage_actual(
  id,sicil,sap_org_kod, position,is_info,fabric,cost_center,persone_type,a_month,a_year,ucret_tip,belge_kod,
  tutar,sgk_gun,ssk_matrah,ss_matrah,yasal_net,odeme_tutari
)
select hranaly.seq_wage_main.nextval, sicil,sap_org_kod,
       position,is_info,fabric,cost_center,persone_type,
       p_month,p_year,ucret_tip,belge_kod,tutar,sgk_gun,
       ssk_matrah,ss_matrah,yasal_net,odeme_tutari
from   xmltable('/ArrayOfBaseUpload/BaseUpload' passing xmltype( p_kalems ) 
columns  
  sicil  number    path './SICIL',  
  sap_org_kod number     path './SAP_ORG_KOD' ,
  position varchar2(100)     path './POSITION',
  is_info varchar2(10) path './IS',
  fabric varchar2(10) path './FABRIC',
  cost_center number     path './COST_CENTER' ,
  persone_type varchar2(10) path './PERSONE_TYPE',
  ucret_tip varchar2(10)     path './UCRET_TIP' ,
  belge_kod varchar2(200) path './BELGE_KOD',
  tutar number     path './TUTAR' ,
  sgk_gun number     path './SGK_GUN' ,
  ssk_matrah number     path './SSK_MATRAH' ,
  ss_matrah number     path './SS_MATRAH' ,
  yasal_net number     path './YASAL_NET' ,
  odeme_tutari number     path './ODEME_TUTARI' 
);

insert into hranaly.wage_actual_detail(id,ref_wage,amount,kalem_kod,a_month,a_year)
  select hranaly.seq_wage_detail.nextval,wa.id,
         p.amount,p.code,p_month,p_year
  from   hranaly.wage_actual wa 
  join   xmltable('/ArrayOfBaseUpload/BaseUpload/DETAILS/DetayUpload' passing xmltype ( p_kalems )              
      columns 
        code varchar2(100) path './CODE', 
        sicil number path './../../SICIL',
        amount number path './AMOUNT'
    ) kalems
  on     wa.a_month= p_month 
  and    wa.a_year=p_year 
  and    wa.sicil=kalems.sicil;

You may even be able to make this into one multi-table insert (insert all) which may be even faster. I'd only look into this if the above changes are still too slow though.

Collectives™ on Stack Overflow

Oracle nested xml parsing

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related