This forum is currently locked. You can't register or post questions at this time. (read more)

Xmltable - xmltype - Replacement

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Guru3i
Senior Member
Posts: 96
Joined: Fri Aug 24, 2007 7:17 am

Xmltable - xmltype - Replacement

Postby Guru3i » Thu Jan 23, 2014 3:50 pm

Hi Tim,
Greetings and hope to see you @ Chennai,India on March 2 as i am from Chennai, India.

Here is the block which has a cursor with xmltype and xmltable clause. However we are facing severe performance issues because of using that part of the cursor. Can you please help us in fixing that, i dont mind the cursor being made into 2/3 different cursors. The Co-Related subquery part with xmltable is the major issue here.
We just need to push the output of this query to a table, I am not able to give you a test case for the same and regret for it. See if you can please help us out. Thanks.

Thanks in advance.

Code: Select all

DECLARE
CURSOR C1 IS
    SELECT 1
    FROM   PGIW_DMS_USER_LVL;
  P_USER_GRP varchar2(240) := 'para1';
  P_USER_ID varchar2(240) := 'para2';
  CURSOR xml_cur is
  SELECT
       HDR.PWTH_PROCESS_ID,
       HDR.PWTH_PWB_CODE,
       WB.PWB_DESC,
       WB.PWB_SR_NO,
       COUNT(*) AS TRANCOUNT
  FROM PGIW_WF_TRAN_HDR     HDR,
       PGIM_WORKFLOW_BUCKET WB,
       PGIM_WF_MAIL_USGRP   MU,
       DMS_WF_ORIGIN        DO
 WHERE WB.PWB_MAIN_BUKT              = HDR.PWTH_PROCESS_ID
   AND DO.PWTH_TRAN_SYS_ID           = HDR.PWTH_TRAN_SYS_ID
   AND WB.PWB_CODE                   = HDR.PWTH_PWB_CODE
    AND WB.PWB_WORKSTEP_ID            = HDR.PWTH_WORKSTEP_ID
   AND NVL(WB.PWB_MAIL_ONLY_YN, '0') = '0'
   AND MU.PWMU_PWB_BUKT_CODE         = HDR.PWTH_PWB_CODE
    AND WB.PWB_CODE                   = MU.PWMU_PWB_BUKT_CODE
   AND MU.PWMU_PWB_BUKT_CODE         = HDR.PWTH_PWB_CODE
   AND MU.PWMU_MAIL_USGRP IS NOT NULL
   AND HDR.PWTH_SYS_ID IN
       (SELECT HDR.PWTH_SYS_ID
          FROM xmltable('r/c' passing
                        xmltype('<r><c>' || replace(REPLACE(HDR.PWTH_TARGET_USGRP || ', ' ||
                                                            DECODE(HDR.PWTH_ESC_LVL_DONE,
                                                                   '1',
                                                                   MU.PWMU_ESC_USGRP_1,
                                                                   '2',
                                                                   MU.PWMU_ESC_USGRP_1,
                                                                   '') || ', ' ||
                                                            DECODE(HDR.PWTH_ESC_LVL_DONE,
                                                                   '2',
                                                                   MU.PWMU_ESC_USGRP_2,
                                                                   ''),
                                                            ''''),
                                                    ', ',
                                                    '</c><c>') || '</c></r>')
                        columns new_str varchar2(50) path '.') d,
               MENU_USER LM
         WHERE (HDR.PWTH_TARGET_USGRP LIKE '%'||upper(P_USER_GRP)||'%' OR
               (HDR.PWTH_ESC_LVL_DONE IN ('1', '2') AND
               (MU.PWMU_ESC_USGRP_1 LIKE '%'||upper(P_USER_GRP)||'%' OR
               MU.PWMU_ESC_USGRP_1 LIKE '%LVL%')) OR
               (HDR.PWTH_ESC_LVL_DONE = '2' AND
               (MU.PWMU_ESC_USGRP_2 LIKE '%'||upper(P_USER_GRP)||'%' OR
               MU.PWMU_ESC_USGRP_2 LIKE '%LVL%')) OR
               HDR.PWTH_TARGET_USER LIKE '%'||upper(P_USER_ID)||'%' OR P_USER_ID = lm.user_id)
           AND (SUBSTR(d.new_str, 1, 3) <> 'LVL' OR
               (SUBSTR(d.new_str, 1, 3) = 'LVL' AND
               to_char(LEVEL - 1) = to_char(SUBSTR(d.new_str, 4))))
         START WITH lm.USER_ID = DO.PWTH_TRAN_USER_ID
            CONNECT BY PRIOR lm.USER_ID = lm.USER_REP_TO
                 AND  PRIOR lm.USER_REP_TO = lm.USER_ID)
 GROUP BY PWTH_PROCESS_ID, PWTH_PWB_CODE, PWB_DESC, PWB_SR_NO
 ORDER BY PWTH_PROCESS_ID, PWB_SR_NO;
 
  M_EXISTS NUMBER;
  TYPE TAB_TYPE IS TABLE OF xml_cur%ROWTYPE INDEX BY BINARY_INTEGER;
  M_REC_TYPE    TAB_TYPE;
 
BEGIN
 IF C1%ISOPEN THEN
    CLOSE C1;
  END IF;
  OPEN C1;
  FETCH C1 INTO M_EXISTS;
  IF C1%FOUND THEN
    DELETE FROM PGIW_DMS_USER_LVL;
  END IF;
  CLOSE C1;

IF xml_cur%ISOPEN THEN
 CLOSE xml_cur;
END IF;

OPEN xml_cur;
 FETCH xml_cur BULK COLLECT INTO M_REC_TYPE; -- consider limit by parameterizing
CLOSE xml_cur;

   dbms_output.put_line(m_rec_type.count);
 
 /* FOR Indx in 1..m_rec_type.count
       loop
         dbms_output.put_line(m_rec_type(indx).PWTH_SYS_ID);
      end loop;*/
       
 /*
  FORALL I IN M_REC_TYPE.first .. M_REC_TYPE.last
    INSERT INTO PGIW_DMS_USER_LVL VALUES M_REC_TYPE(I);
  COMMIT;

 For indx in (Select * from Pgiw_dms_user_lvl)
  loop
    dbms_output.put_line(indx.pwth_process_id || ' * '|| indx.pwth_pwb_code || ' * ' || indx.pwb_desc || ' * ' || indx.pwb_sr_no || ' * '|| indx.trancount );
   end loop;
  */   
   
END;
 

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Xmltable - xmltype - Replacement

Postby Tim... » Thu Jan 23, 2014 6:24 pm

Hi.

It's kind-of difficult to see what this does without any context, like a test case, but I don't really understand what you are trying to achieve with the XMLTABLE and why it is even necessary.

From what I can see, you are creating some XML on the fly, that isn't even XML really. It is CSV data, put into an XML wrapper. You are then converting it to an XMLTYPE, to feed into the XMLTABLE. This is getting joined against a real table and filtered using a whole bunch of substrings, that probably wouldn't be necessary if you hadn't first generated the XML.

I may have missed the point, but this looks massively over-engineered and totally unnecessary. Not sure how to tell you to proceed because I think either I've missed something fundamental to the whole process, or that whole subselect is totally unnecessary and needs to be thrown away and start again...

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 3 guests

cron