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

Answer 1

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.