All geek questions in one place

PL \ SQL XMLTable Performance

I have a function that parses an XMLTYPE variable, and for each message adds a CLOB with a specific structure for each tag in the XMLTYPE variable. Like this:

FUNCTION myFunc (px_Header      IN     VARCHAR2,
                                px_Block       IN     XMLTYPE,
                                pn_numLines      OUT PLS_INTEGER)
      RETURN CLOB
   IS
      lcl_return   CLOB := EMPTY_CLOB;
   BEGIN
      pn_numLines := 0;

      FOR item
         IN (          SELECT RPAD (NVL (RECEIPTNUMBER, ' '), 20) AS RECEIPTNUMBER,
                              RPAD (NVL (COMPANYCODE, ' '), 3) AS COMPANYCODE,
                              RPAD (NVL (BRAND, ' '), 3) AS BRAND,
                              RPAD (NVL (POLICYNUMBER, ' '), 20) AS POLICYNUMBER,
                              RPAD (NVL (CLAIMNUMBER, ' '), 20) AS CLAIMNUMBER,
                              RECEIPTAMOUNT
                                 AS receiptAmount
                         FROM XMLTABLE (
                                 'INT_DATA/Item'
                                 PASSING px_Block
                                 COLUMNS RECEIPTNUMBER   VARCHAR2 (20)
                                                            PATH 'RECEIPTNUMBER',
                                         COMPANYCODE     VARCHAR2 (3)
                                                            PATH 'COMPANYCODE',
                                         BRAND           VARCHAR2 (3) PATH 'BRAND',
                                         POLICYNUMBER    VARCHAR2 (20)
                                                            PATH 'POLICYNUMBER',
                                         CLAIMNUMBER     VARCHAR2 (20)
                                                            PATH 'CLAIMNUMBER',
                                         RECEIPTAMOUNT   VARCHAR2 (15)
                                                            PATH 'RECEIPTAMOUNT'))
      LOOP
         lcl_return:=
               lcl_return
            || px_Header
            || 'B2'
            || item.RECEIPTNUMBER
            || item.COMPANYCODE
            || item.BRAND
            || item.POLICYNUMBER
            || item.CLAIMNUMBER
            || item.RECEIPTAMOUNT
            || CHR (13)
            || CHR (10);
         pn_numLines := pn_numLines + 1;
      END LOOP;

      RETURN lcl_return;
   END myFunc ;

This works well if I have a small one px_Block. But I have a case where I can have large XMLTYPE, and this function takes a lot of time. I am new to XMLType and XMLTable. Is there anything I can do to improve performance. Maybe using the BULK COLLECT statement?

Thanks in advance, Philip

EDIT1: Here is an XML example for only two instances.

<INT_DATA>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>72972</POLICYNUMBER>
  <CLAIMNUMBER>2015101504</CLAIMNUMBER>
  <RECEIPTAMOUNT>-10.00</RECEIPTAMOUNT>
 </Item>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>73785</POLICYNUMBER>
  <CLAIMNUMBER>2015101505</CLAIMNUMBER>
  <RECEIPTAMOUNT>-22.50</RECEIPTAMOUNT>
 </Item>
</INT_DATA>

EDIT2: I made some changes to my function with improved improvements of 20%. I changed the way CLOB was added using a temporary CLOB. But better solutions will be helpful.

FUNCTION myFunc (px_Header      IN     VARCHAR2,
                                px_Block       IN     XMLTYPE,
                                pn_numLines      OUT PLS_INTEGER)
      RETURN CLOB
   IS
      lcl_return   CLOB := EMPTY_CLOB;
      v_tmp_clob    CLOB := EMPTY_CLOB;
   BEGIN
      pn_numLines := 0;

      FOR item
         IN (          SELECT RPAD (NVL (RECEIPTNUMBER, ' '), 20) AS RECEIPTNUMBER,
                              RPAD (NVL (COMPANYCODE, ' '), 3) AS COMPANYCODE,
                              RPAD (NVL (BRAND, ' '), 3) AS BRAND,
                              RPAD (NVL (POLICYNUMBER, ' '), 20) AS POLICYNUMBER,
                              RPAD (NVL (CLAIMNUMBER, ' '), 20) AS CLAIMNUMBER,
                              RECEIPTAMOUNT
                                 AS receiptAmount
                         FROM XMLTABLE (
                                 'INT_DATA/Item'
                                 PASSING px_Block
                                 COLUMNS RECEIPTNUMBER   VARCHAR2 (20)
                                                            PATH 'RECEIPTNUMBER',
                                         COMPANYCODE     VARCHAR2 (3)
                                                            PATH 'COMPANYCODE',
                                         BRAND           VARCHAR2 (3) PATH 'BRAND',
                                         POLICYNUMBER    VARCHAR2 (20)
                                                            PATH 'POLICYNUMBER',
                                         CLAIMNUMBER     VARCHAR2 (20)
                                                            PATH 'CLAIMNUMBER',
                                         RECEIPTAMOUNT   VARCHAR2 (15)
                                                            PATH 'RECEIPTAMOUNT'))
      LOOP
         v_tmp_clob :=
           TO_CLOB (px_Header)
        || TO_CLOB ('B2')
        || TO_CLOB (item.RECEIPTNUMBER)
        || TO_CLOB (item.COMPANYCODE)
        || TO_CLOB (item.BRAND)
        || TO_CLOB (item.POLICYNUMBER)
        || TO_CLOB (item.CLAIMNUMBER)
        || TO_CLOB (item.RECEIPTAMOUNT)
        || CHR (13)
        || CHR (10);

     lcl_return := lcl_return || v_tmp_clob;

         pn_numLines := pn_numLines + 1;
      END LOOP;

      RETURN lcl_return;
   END myFunc ;
+4
3

XMLTABLE , Oracle DOM Parser XML. , XML . DBMS_XMLSTORE. SAX Parser, ( ).

SAX Parser, XML .

, - , 50-100 - DOM Parser .

+4

XML , dbms_xmlsave XML- .

, XML.

-- Create table
create table TEST
(
  receiptnumber NUMBER,
  companycode   NUMBER,
  brand         VARCHAR2(10),
  policynumber  NUMBER,
  claimnumber   NUMBER,
  receiptamount NUMBER
)

dbms_xmlsave .

declare
   l_clob   clob;
   l_rows  number;
   l_insctx dbms_xmlsave.ctxtype;
begin
   l_clob := '<INT_DATA>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>72972</POLICYNUMBER>
  <CLAIMNUMBER>2015101504</CLAIMNUMBER>
  <RECEIPTAMOUNT>-10.00</RECEIPTAMOUNT>
 </Item>
 <Item>
  <RECEIPTNUMBER>1</RECEIPTNUMBER>
  <COMPANYCODE>148</COMPANYCODE>
  <BRAND>006</BRAND>
  <POLICYNUMBER>73785</POLICYNUMBER>
  <CLAIMNUMBER>2015101505</CLAIMNUMBER>
  <RECEIPTAMOUNT>-22.50</RECEIPTAMOUNT>
 </Item>
</INT_DATA>';

   l_insctx := dbms_xmlsave.newcontext('TEST');
   dbms_xmlsave.setrowtag(l_insctx, 'Item');
   l_rows := dbms_xmlsave.insertxml(l_insctx, l_clob);
   dbms_xmlsave.closecontext(l_insctx);
end;

, .

+2

I found a problem and solved it. The delay was not in the XML parser, but in the CLOB append, as @Rene said.

Using '||' it takes a long time to add varchar2 to the CLOB. So, I need to use DBMS_LOB.append. But for this I need something in the CLOB. I cannot add an empty CLOB.

This is why I am fixing this:

FUNCTION myFunc (px_Header      IN     VARCHAR2,
                            px_Block       IN     XMLTYPE,
                            pn_numLines      OUT PLS_INTEGER)
  RETURN CLOB
IS
  lcl_return   CLOB;
  v_tmpVarchar   VARCHAR2 (32000);
  lv_line        VARCHAR2 (32000);
BEGIN
  pn_numLines := 0;

  FOR item
     IN (          SELECT RPAD (NVL (RECEIPTNUMBER, ' '), 20) AS RECEIPTNUMBER,
                          RPAD (NVL (COMPANYCODE, ' '), 3) AS COMPANYCODE,
                          RPAD (NVL (BRAND, ' '), 3) AS BRAND,
                          RPAD (NVL (POLICYNUMBER, ' '), 20) AS POLICYNUMBER,
                          RPAD (NVL (CLAIMNUMBER, ' '), 20) AS CLAIMNUMBER,
                          RECEIPTAMOUNT
                             AS receiptAmount
                     FROM XMLTABLE (
                             'INT_DATA/Item'
                             PASSING px_Block
                             COLUMNS RECEIPTNUMBER   VARCHAR2 (20)
                                                        PATH 'RECEIPTNUMBER',
                                     COMPANYCODE     VARCHAR2 (3)
                                                        PATH 'COMPANYCODE',
                                     BRAND           VARCHAR2 (3) PATH 'BRAND',
                                     POLICYNUMBER    VARCHAR2 (20)
                                                        PATH 'POLICYNUMBER',
                                     CLAIMNUMBER     VARCHAR2 (20)
                                                        PATH 'CLAIMNUMBER',
                                     RECEIPTAMOUNT   VARCHAR2 (15)
                                                        PATH 'RECEIPTAMOUNT'))
  LOOP
     lv_line :=
           px_Header
        || 'B2'
        || item.RECEIPTNUMBER
        || item.COMPANYCODE
        || item.BRAND
        || item.POLICYNUMBER
        || item.CLAIMNUMBER
        || item.RECEIPTAMOUNT
        || CHR (13)
        || CHR (10);
     pn_numLines := pn_numLines + 1;

     appendCLOB (lcl_retorno, lv_tmpVarchar, lv_linha);

  END LOOP;

  RETURN lcl_return;
END myFunc ;

And the appendCLOB function looks like this:

 PROCEDURE appendCLOB (pcl_clob   IN OUT NOCOPY CLOB,
                         pv_vc      IN OUT NOCOPY VARCHAR2,
                         pv_text                  VARCHAR2)
   IS
   BEGIN
      pv_vc := pv_vc || pv_text;
   EXCEPTION
      WHEN VALUE_ERROR
      THEN
         IF pcl_clob IS NULL
         THEN
            -- Add the first varchar
            pcl_clob := pv_vc;
         ELSE
            -- If the clob is not empty, uses the DBMS_LOB.append function
            DBMS_LOB.append (pcl_clob, pv_vc);
            pv_vc := pv_text;
         END IF;
   END;

For 36k instances in XML, the My function takes less than a minute. Thanks everyone!

+1
source

Source: https://habr.com/ru/post/1617433/

More articles:


All Articles