7 Replies Latest reply: Dec 16, 2012 7:09 PM by Kunal RSS

    lead function equivalent in Oracle to handle Clob datatype in xml

    Kunal
      Hi All,

      I was trying to aggregate strings using the LISTAGG function but obviously when the strlen was more then 4000 it bombed. So i used the UDF clobagg to achieve it. But now i am stuck with a problem i was also using LEAD window function on the value returned by LISTAGG. Now since CLOBAGG returns clob the LEAD is not able to work on it. Could anyone please suggest what could be the solution

      Here i the code
      DECLARE
      
        xml_header    clob := '<?xml version="1.0" encoding="UTF-8" standalone="no"?>
                              <!DOCTYPE gsa-template SYSTEM "dynamosystemresource:/atg/dtds/gsa/gsa_1.0.dtd">';
      
        cursor c_invoice is
        WITH item_data
        AS
           (SELECT A.*,
                   LEAD(sku_list, 1, 0)        -- Here is the problem
                      OVER(PARTITION BY NVL(item_parent, item) ORDER BY lvl) sku_list1
              FROM (SELECT a.lvl,
                           a.item,
                           a.item_parent,
                           a.item_desc,
                           --LISTAGG(a.item, ',') WITHIN GROUP (ORDER BY a.item) OVER(PARTITION BY a.item_parent) sku_list,
                           clobagg(a.item||',') OVER(PARTITION BY a.item_parent order by a.item) sku_list,    -- LISTAGG replaced by CLOBAGG
                           TO_CHAR(a.creationdate, 'MM/DD/YYYY HH:MI AM') creationDate,
                           a.dept,
                           a.class,
                           a.subclass,
                           a.diff_1,
                           a.diff_2,
                           a.diff_3,
                           a.standard_retail,
                           a.division,
                           a.season_id,
                           a.season_desc,
                           a.phase_id,
                           a.phase_desc,
                           a.item_sec_desc,
                           a.curr_phase
                      FROM ct_atg_item_export a 
                     WHERE a.export_ind = 'N'
                   ) A                                                      --table definition till this part given below
               )                                                                 
         SELECT XMLElement("gsa-template",
                        Xmlelement("import-items",
            (SELECT XMLAGG(Xmlelement("add-item",
                        XMLAttributes('product' AS "item-descriptor",
                                      a.item AS "id"
                                     ),    
                        XMLElement("set-property",
                                   XMLAttributes('displayNameDefault' AS "name" ),
                                   XMLCDATA(a.item_desc)
                                  ),
                        XMLElement("set-property",
                                   XMLAttributes('longDescriptionDefault' AS "name" ),
                                   XMLCDATA(a.item_desc ||'-'||'webdisplay')
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('childSKUs' As "name" ),
                                   XMLCDATA(RTRIM(a.sku_list1,','))
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('creationDate' As "name" ),
                                   XMLCDATA(a.creationDate) 
                                   ),
                        XMLElement("set-property",
                                  Xmlattributes('hazmat' As "name" ),
                                  XMLCDATA('false')
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('fabric' As "name" ),
                                   XMLCDATA('01=50,02=25,04=25')
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('translations' As "name" ),
                                   XMLCDATA('fr='||a.item||'_fr')
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('seasonId' As "name" ),
                                   XMLCDATA(a.season_id)
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('seasonDesc' As "name" ),
                                   XMLCDATA(a.season_desc)
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('phaseId' As "name" ),
                                   XMLCDATA(a.phase_id)
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('phaseDesc' As "name" ),
                                   XMLCDATA(a.phase_desc)
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('division' As "name" ),
                                   XMLCDATA(a.division)
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('department' As "name" ),
                                   XMLCDATA(a.dept)
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('class' AS "name" ),
                                   XMLCDATA(a.class)
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('subclass' AS "name" ),
                                   XMLCDATA(a.subclass)
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('sizeGroup' AS "name" ),
                                   XMLCDATA(a.diff_2)
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('nonMerchandise' AS "name" ),
                                   XMLCDATA('false')
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('nonReturnable' AS "name" ),
                                   XMLCDATA('false')
                                  ),
                        XMLElement("set-property",
                                   Xmlattributes('taxable' AS "name" ),
                                   XMLCDATA('true')
                                  )                                                                   
                     )).getclobval() AS product
            FROM item_data a where lvl=1),
         (SELECT XMLAGG(Xmlelement("add-item",
                         XMLAttributes('productTranslation' AS "item-descriptor",
                                       item||'_fr' AS "id"
                                      ),
                         XMLElement("set-property",
                                    XMLAttributes('displayName' AS "name" ),
                                    XMLCDATA(item_sec_desc)
                                   ),
                         XMLElement("set-property",
                                    XMLAttributes('longDescription' AS "name" ),
                                    XMLCDATA(item_sec_desc ||'-'||'webdisplay')
                                   )
                       )).getclobval() AS productTranslation
            FROM item_data 
           WHERE lvl=1),
         (SELECT XMLAGG(Xmlelement("add-item",
                         XMLAttributes('sku' AS "item-descriptor",
                                       a.item AS "id"
                                      ),
                         XMLElement("set-property",
                                    XMLAttributes('displayNameDefault' AS "name" ),
                                    XMLCDATA(a.item_desc)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('creationDate' AS "name" ),
                                    XMLCDATA(a.creationDate)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('supplier' AS "name" ),
                                    XMLCDATA(b.supplier)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('wholesalePrice' AS "name" ),
                                    XMLCDATA(a.standard_retail)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('factory' AS "name" ),
                                    XMLCDATA(a.diff_3)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('color' AS "name" ),
                                    XMLCDATA(a.diff_1)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('colorDescription' AS "name" ),
                                    XMLCDATA(d.diff_desc)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('displayColorDescDefault' AS "name" ),
                                    XMLCDATA(d.diff_desc||'_web')
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('size' AS "name" ),
                                    XMLCDATA(a.diff_2)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('sizeDescription' AS "name" ),
                                    XMLCDATA(e.diff_desc)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('manufacturingCountry' AS "name" ),
                                    XMLCDATA(c.manu_country_id)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('shippedFromCountry' AS "name" ),
                                    XMLCDATA(b.origin_country_id)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('length' AS "name" ),
                                    XMLCDATA('1.0')
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('height' AS "name" ),
                                    XMLCDATA('1.0')
                                   ),                          
                         XMLElement("set-property",
                                    Xmlattributes('width' AS "name" ),
                                    XMLCDATA('1.0')
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('dimensionsUOM' AS "name" ),
                                    XMLCDATA('IN')
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('weight' AS "name" ),
                                    XMLCDATA('1.0')
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('weightUOM' AS "name" ),
                                    XMLCDATA('LB')
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('harmonizedTariffs' AS "name" ),
                                    XMLCDATA('Duty')
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('harmonizedTariffsValue' AS "name" ),
                                    XMLCDATA('1.23')
                                   ),                                
                         XMLElement("set-property",
                                    Xmlattributes('translations' AS "name" ),
                                    XMLCDATA('fr='||a.item||'_fr')
                                   ),                                
                         XMLElement("set-property",
                                    Xmlattributes('taxCode' AS "name" ),
                                    XMLCDATA('01')
                                   ),                                
                         XMLElement("set-property",
                                    Xmlattributes('avgCost' AS "name" ),
                                    XMLCDATA(a.standard_retail+5 )
                                   )                             
                       )).getclobval() AS sku 
            FROM item_data a, item_supp_country b, item_supp_manu_country c, diff_ids d, diff_ids e
           WHERE a.lvl=2
             AND a.item = b.item
             AND b.primary_supp_ind = 'Y'
             AND b.primary_country_ind='Y'
             AND c.item = b.item
             AND c.supplier = b.supplier
             AND c.primary_manu_ctry_ind='Y'
             AND a.diff_1 = d.diff_id AND d.diff_type='C'
             AND a.diff_2 = e.diff_id AND e.diff_type='S'),
         (SELECT XMLAGG(Xmlelement("add-item",
                         XMLAttributes('skuTranslation' AS "item-descriptor",
                                       a.item||'_fr' AS "id"
                                      ),
                         XMLElement("set-property",
                                    XMLAttributes('displayName' AS "name" ),
                                    XMLCDATA(item_sec_desc)
                                   ),
                         XMLElement("set-property",
                                    Xmlattributes('displayColorDesc' AS "name" ),
                                    XMLCDATA('a.displayColorDesc_french')
                                   )                             
                       )).getclobval() AS skuTranslation 
            FROM item_data a
           WHERE a.lvl=2)
           )
           ).getclobval() as invoice_xml 
          FROM dual;
      BEGIN
       
        -- create the temporary clob : 
        dbms_lob.createtemporary(tmp_file, true);
         
        -- append header
        dbms_lob.append(tmp_file, xml_header);
       
        -- fetch the invoices and append : 
        for r in c_invoice 
        loop
          --dbms_lob.append(tmp_file, xml_prolog);
          dbms_lob.append(tmp_file, r.invoice_xml);
        end loop;
        
        -- write file to directory : 
        dbms_xslprocessor.clob2file(tmp_file,'TEST_EXT_DATA', 'invoice_20121213.xml', nls_charset_id('AL32UTF8'));
        dbms_lob.freetemporary(tmp_file);
       
      END;
      /
      to create the table for select
      CREATE TABLE CUSTOM.CT_ATG_ITEM_EXPORT
      (
        LVL              NUMBER(2)                    NOT NULL,
        ITEM             VARCHAR2(25 BYTE)            NOT NULL,
        ITEM_PARENT      VARCHAR2(25 BYTE),
        ITEM_DESC        VARCHAR2(250 BYTE),
        CREATIONDATE     DATE,
        DEPT             NUMBER(4)                    NOT NULL,
        CLASS            NUMBER(4),
        SUBCLASS         NUMBER(4),
        DIFF_1           VARCHAR2(10 BYTE),
        DIFF_2           VARCHAR2(10 BYTE),
        DIFF_3           VARCHAR2(10 BYTE),
        STANDARD_RETAIL  NUMBER(20,4),
        DIVISION         NUMBER(4),
        SEASON_ID        NUMBER(3),
        SEASON_DESC      VARCHAR2(120 BYTE),
        PHASE_ID         NUMBER(3),
        PHASE_DESC       VARCHAR2(120 BYTE),
        ITEM_SEC_DESC    VARCHAR2(250 BYTE),
        CURR_PHASE       NUMBER(2),
        EXPORT_IND       VARCHAR2(1 BYTE),
        LOAD_DATE        DATE,
        EXPORT_DATE      DATE,
        LOAD_DATETIME    DATE
      )
      Few sample Inserts..
      Insert into CT_ATG_ITEM_EXPORT
         (LVL, ITEM, ITEM_DESC, CREATIONDATE, DEPT, 
          CLASS, SUBCLASS, DIFF_1, DIFF_2, DIFF_3, 
          STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, PHASE_ID, 
          PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, LOAD_DATE, 
          EXPORT_DATE, LOAD_DATETIME)
       Values
         (1, '112392C', 'CLUSTER HOOP/LEAF/CLR BEADS NK', TO_DATE('10/16/2012 16:27:11', 'MM/DD/YYYY HH24:MI:SS'), 502, 
          23, 1, 'ALLCOLOR', '6', 'ALLFACTORY', 
          12.9, 50, 102, 'FALL 2010', 1, 
          'FALL 2010', 'COLLIER ANNEAUX/FEUILLES/BILLE', 1, 'N', TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
          TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:05', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CT_ATG_ITEM_EXPORT
         (LVL, ITEM, ITEM_DESC, CREATIONDATE, DEPT, 
          CLASS, SUBCLASS, DIFF_1, DIFF_2, DIFF_3, 
          STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, PHASE_ID, 
          PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, LOAD_DATE, 
          EXPORT_DATE, LOAD_DATETIME)
       Values
         (1, '112510', 'BEADS&MESH STATION NK SET', TO_DATE('10/16/2012 16:27:11', 'MM/DD/YYYY HH24:MI:SS'), 502, 
          23, 5, 'ALLCOLOR', '6', 'ALLFACTORY', 
          14.9, 50, 102, 'FALL 2010', 1, 
          'FALL 2010', 'BOUCLE D OREILLE ET COLLIER', 1, 'N', TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
          TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:05', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CT_ATG_ITEM_EXPORT
         (LVL, ITEM, ITEM_DESC, CREATIONDATE, DEPT, 
          CLASS, SUBCLASS, DIFF_1, DIFF_2, DIFF_3, 
          STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, PHASE_ID, 
          PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, LOAD_DATE, 
          EXPORT_DATE, LOAD_DATETIME)
       Values
         (1, '112510C', 'BEADS&MESH STATION NK SET', TO_DATE('10/16/2012 16:27:11', 'MM/DD/YYYY HH24:MI:SS'), 502, 
          23, 5, 'ALLCOLOR', '6', 'ALLFACTORY', 
          14.9, 50, 102, 'FALL 2010', 1, 
          'FALL 2010', 'BOUCLE D OREILLE ET COLLIER', 1, 'N', TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
          TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:05', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CT_ATG_ITEM_EXPORT
         (LVL, ITEM, ITEM_DESC, CREATIONDATE, DEPT, 
          CLASS, SUBCLASS, DIFF_1, DIFF_2, DIFF_3, 
          STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, PHASE_ID, 
          PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, LOAD_DATE, 
          EXPORT_DATE, LOAD_DATETIME)
       Values
         (1, '112975', '10PC CHARM BRACELET', TO_DATE('10/16/2012 16:27:11', 'MM/DD/YYYY HH24:MI:SS'), 702, 
          23, 2, 'ALLCOLOR', '6', 'ALLFACTORY', 
          6.9, 70, 101, 'SPRING 2010', 1, 
          'SPRING 2010', '10PC CHARM BRACELET', 1, 'N', TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
          TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:05', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CT_ATG_ITEM_EXPORT
         (LVL, ITEM, ITEM_DESC, CREATIONDATE, DEPT, 
          CLASS, SUBCLASS, DIFF_1, DIFF_2, DIFF_3, 
          STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, PHASE_ID, 
          PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, LOAD_DATE, 
          EXPORT_DATE, LOAD_DATETIME)
       Values
         (1, '113067', 'MESH 3 BANGLE SET BRACELET', TO_DATE('10/16/2012 16:27:11', 'MM/DD/YYYY HH24:MI:SS'), 502, 
          23, 2, 'ALLCOLOR', '6', 'ALLFACTORY', 
          12.9, 50, 111, 'SPRING 2011', 1, 
          'SPRING 2011', 'BRACELET', 1, 'N', TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
          TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:05', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CT_ATG_ITEM_EXPORT
         (LVL, ITEM, ITEM_DESC, CREATIONDATE, DEPT, 
          CLASS, SUBCLASS, DIFF_1, DIFF_2, DIFF_3, 
          STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, PHASE_ID, 
          PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, LOAD_DATE, 
          EXPORT_DATE, LOAD_DATETIME)
       Values
         (1, '113067C', 'MESH 3 BANGLE SET BRACELET', TO_DATE('10/16/2012 16:27:11', 'MM/DD/YYYY HH24:MI:SS'), 502, 
          23, 2, 'ALLCOLOR', '6', 'ALLFACTORY', 
          12.9, 50, 111, 'SPRING 2011', 1, 
          'SPRING 2011', 'BRACELET', 1, 'N', TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
          TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:05', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CT_ATG_ITEM_EXPORT
         (LVL, ITEM, ITEM_DESC, CREATIONDATE, DEPT, 
          CLASS, SUBCLASS, DIFF_1, DIFF_2, DIFF_3, 
          STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, PHASE_ID, 
          PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, LOAD_DATE, 
          EXPORT_DATE, LOAD_DATETIME)
       Values
         (1, '113096', 'LARGE FLOWERS W/PEARL RING', TO_DATE('10/16/2012 16:27:11', 'MM/DD/YYYY HH24:MI:SS'), 502, 
          23, 3, 'ALLCOLOR', '6', 'ALLFACTORY', 
          12.9, 50, 102, 'FALL 2010', 1, 
          'FALL 2010', 'BANGUE AVEC FLEUR ET PERLE', 1, 'N', TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
          TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:05', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CT_ATG_ITEM_EXPORT
         (LVL, ITEM, ITEM_DESC, CREATIONDATE, DEPT, 
          CLASS, SUBCLASS, DIFF_1, DIFF_2, DIFF_3, 
          STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, PHASE_ID, 
          PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, LOAD_DATE, 
          EXPORT_DATE, LOAD_DATETIME)
       Values
         (1, '113096C', 'LARGE FLOWERS W/PEARLS RING', TO_DATE('10/16/2012 16:27:11', 'MM/DD/YYYY HH24:MI:SS'), 502, 
          23, 3, 'ALLCOLOR', '6', 'ALLFACTORY', 
          12.9, 50, 102, 'FALL 2010', 1, 
          'FALL 2010', 'BANGUE AVEC FLEUR/PERLE', 1, 'N', TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
          TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:05', 'MM/DD/YYYY HH24:MI:SS'));
      Insert into CT_ATG_ITEM_EXPORT
         (LVL, ITEM, ITEM_DESC, CREATIONDATE, DEPT, 
          CLASS, SUBCLASS, DIFF_1, DIFF_2, DIFF_3, 
          STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, PHASE_ID, 
          PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, LOAD_DATE, 
          EXPORT_DATE, LOAD_DATETIME)
       Values
         (1, '113190', 'LINK CHAIN STRETCH BRACELET', TO_DATE('10/16/2012 16:27:11', 'MM/DD/YYYY HH24:MI:SS'), 502, 
          23, 2, 'ALLCOLOR', '6', 'ALLFACTORY', 
          12.9, 50, 102, 'FALL 2010', 1, 
          'FALL 2010', 'BRACELET A CHAINONS', 1, 'N', TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
          TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:05', 'MM/DD/YYYY HH24:MI:SS'));
      COMMIT;
        • 1. Re: lead function equivalent in Oracle to handle Clob datatype in xml
          odie_63
          Hi,

          Sounds like an interesting problem.
          Thanks for the sample data.

          I don't fully understand the whole requirement behind using LEAD/LISTAGG but I suspect there's a simpler approach.

          Could you explain a little bit further?
          Maybe some sample data having ITEM_PARENT would help?

          Another question : you're generating a whole part of the XML in escaped form, is it the intended outcome?
          • 2. Re: lead function equivalent in Oracle to handle Clob datatype in xml
            Kunal
            Thanks for your quick reply Odie.

            What i am basically trying to achieve with the lead function is aggregation all the item child of the item parent. some cases the no of item child are more than 700 which has forced me to use the clobagg. Now it returns correct data when is do LEAD(to_char(a.skulist),1,0) but it doesnt solve my purpose as the to_char() fails when the skulist is more than 4000.

            Inserts with item parent and item its child
            Insert into CT_ATG_ITEM_EXPORT
               (LVL, ITEM, ITEM_DESC, CREATIONDATE, DEPT, 
                CLASS, SUBCLASS, DIFF_1, DIFF_2, DIFF_3, 
                STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, PHASE_ID, 
                PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, LOAD_DATE, 
                EXPORT_DATE, LOAD_DATETIME)
             Values
               (1, '83083R', 'SINGLE BUCKLE BRAIDED BELT', TO_DATE('10/16/2012 16:27:11', 'MM/DD/YYYY HH24:MI:SS'), 502, 
                22, 2, 'ALLCOLOR', '2', 'ALLFACTORY', 
                17.9, 50, 101, 'SPRING 2010', 1, 
                'SPRING 2010', 'CEINTURE TRESSE', 1, 'N', TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:38', 'MM/DD/YYYY HH24:MI:SS'));
            COMMIT;
            Insert into CT_ATG_ITEM_EXPORT
               (LVL, ITEM, ITEM_PARENT, ITEM_DESC, CREATIONDATE, 
                DEPT, CLASS, SUBCLASS, DIFF_1, DIFF_2, 
                DIFF_3, STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, 
                PHASE_ID, PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, 
                LOAD_DATE, EXPORT_DATE, LOAD_DATETIME)
             Values
               (2, '1118310', '83083R', 'SINGLE BUCKLE BRAIDED BELT', TO_DATE('10/16/2012 16:53:51', 'MM/DD/YYYY HH24:MI:SS'), 
                502, 22, 2, '599', 'XS', 
                '100098', 17.9, 50, 101, 'SPRING 2010', 
                1, 'SPRING 2010', 'CEINTURE TRESSE', 1, 'N', 
                TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:51', 'MM/DD/YYYY HH24:MI:SS'));
            Insert into CT_ATG_ITEM_EXPORT
               (LVL, ITEM, ITEM_PARENT, ITEM_DESC, CREATIONDATE, 
                DEPT, CLASS, SUBCLASS, DIFF_1, DIFF_2, 
                DIFF_3, STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, 
                PHASE_ID, PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, 
                LOAD_DATE, EXPORT_DATE, LOAD_DATETIME)
             Values
               (2, '1118320', '83083R', 'SINGLE BUCKLE BRAIDED BELT', TO_DATE('10/16/2012 16:53:51', 'MM/DD/YYYY HH24:MI:SS'), 
                502, 22, 2, '599', 'S', 
                '100098', 17.9, 50, 101, 'SPRING 2010', 
                1, 'SPRING 2010', 'CEINTURE TRESSE', 1, 'N', 
                TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:51', 'MM/DD/YYYY HH24:MI:SS'));
            Insert into CT_ATG_ITEM_EXPORT
               (LVL, ITEM, ITEM_PARENT, ITEM_DESC, CREATIONDATE, 
                DEPT, CLASS, SUBCLASS, DIFF_1, DIFF_2, 
                DIFF_3, STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, 
                PHASE_ID, PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, 
                LOAD_DATE, EXPORT_DATE, LOAD_DATETIME)
             Values
               (2, '1118330', '83083R', 'SINGLE BUCKLE BRAIDED BELT', TO_DATE('10/16/2012 16:53:51', 'MM/DD/YYYY HH24:MI:SS'), 
                502, 22, 2, '599', 'M', 
                '100098', 17.9, 50, 101, 'SPRING 2010', 
                1, 'SPRING 2010', 'CEINTURE TRESSE', 1, 'N', 
                TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:51', 'MM/DD/YYYY HH24:MI:SS'));
            Insert into CT_ATG_ITEM_EXPORT
               (LVL, ITEM, ITEM_PARENT, ITEM_DESC, CREATIONDATE, 
                DEPT, CLASS, SUBCLASS, DIFF_1, DIFF_2, 
                DIFF_3, STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, 
                PHASE_ID, PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, 
                LOAD_DATE, EXPORT_DATE, LOAD_DATETIME)
             Values
               (2, '1118340', '83083R', 'SINGLE BUCKLE BRAIDED BELT', TO_DATE('10/16/2012 16:53:51', 'MM/DD/YYYY HH24:MI:SS'), 
                502, 22, 2, '599', 'L', 
                '100098', 17.9, 50, 101, 'SPRING 2010', 
                1, 'SPRING 2010', 'CEINTURE TRESSE', 1, 'N', 
                TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:51', 'MM/DD/YYYY HH24:MI:SS'));
            Insert into CT_ATG_ITEM_EXPORT
               (LVL, ITEM, ITEM_PARENT, ITEM_DESC, CREATIONDATE, 
                DEPT, CLASS, SUBCLASS, DIFF_1, DIFF_2, 
                DIFF_3, STANDARD_RETAIL, DIVISION, SEASON_ID, SEASON_DESC, 
                PHASE_ID, PHASE_DESC, ITEM_SEC_DESC, CURR_PHASE, EXPORT_IND, 
                LOAD_DATE, EXPORT_DATE, LOAD_DATETIME)
             Values
               (2, '1118350', '83083R', 'SINGLE BUCKLE BRAIDED BELT', TO_DATE('10/16/2012 16:53:51', 'MM/DD/YYYY HH24:MI:SS'), 
                502, 22, 2, '599', 'XL', 
                '100098', 17.9, 50, 101, 'SPRING 2010', 
                1, 'SPRING 2010', 'CEINTURE TRESSE', 1, 'N', 
                TO_DATE('12/11/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/13/2012 10:18:14', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2012 17:39:51', 'MM/DD/YYYY HH24:MI:SS'));
            COMMIT;
            • 3. Re: lead function equivalent in Oracle to handle Clob datatype in xml
              Kunal
              Hi Odie,

              I tried to get rid of LEAD by using self join on item data but it failed to get the desired output. Do you think anything else can be used to achieve it. And for the escaped format of the xml i have removed the multiple .getclobval and just kept one at the final output, that solved the issue. Please let me know if you have thought of an alternative!

              Thanks a lot
              Kunal
              • 4. Re: lead function equivalent in Oracle to handle Clob datatype in xml
                odie_63
                Hi,
                I tried to get rid of LEAD by using self join on item data but it failed to get the desired output. Do you think anything else can be used to achieve it.
                That's what I was about to suggest too.

                For example :
                SQL> select t.lvl
                  2       , t.item
                  3       , t.item_parent
                  4       , t.item_desc
                  5       , (
                  6           select rtrim(
                  7                    xmlcast(
                  8                      xmlagg(xmlelement("e", c.item||','))
                  9                      as clob
                 10                    )
                 11                  , ','
                 12                  )
                 13           from ct_atg_item_export c
                 14           where c.item_parent = t.item
                 15           and c.export_ind = 'N'
                 16         ) as sku_list1
                 17  from ct_atg_item_export t
                 18  where t.export_ind = 'N' ;
                 
                LVL ITEM                      ITEM_PARENT               ITEM_DESC                           SKU_LIST1
                --- ------------------------- ------------------------- ----------------------------------- --------------------------------------------
                  1 112392C                                             CLUSTER HOOP/LEAF/CLR BEADS NK      
                  1 112510                                              BEADS&MESH STATION NK SET           
                  1 112510C                                             BEADS&MESH STATION NK SET           
                  1 112975                                              10PC CHARM BRACELET                 
                  1 113067                                              MESH 3 BANGLE SET BRACELET          
                  1 113067C                                             MESH 3 BANGLE SET BRACELET          
                  1 113096                                              LARGE FLOWERS W/PEARL RING          
                  1 113096C                                             LARGE FLOWERS W/PEARLS RING         
                  1 113190                                              LINK CHAIN STRETCH BRACELET         
                  1 83083R                                              SINGLE BUCKLE BRAIDED BELT          1118310,1118320,1118330,1118340,1118350
                  2 1118310                   83083R                    SINGLE BUCKLE BRAIDED BELT          
                  2 1118320                   83083R                    SINGLE BUCKLE BRAIDED BELT          
                  2 1118330                   83083R                    SINGLE BUCKLE BRAIDED BELT          
                  2 1118340                   83083R                    SINGLE BUCKLE BRAIDED BELT          
                  2 1118350                   83083R                    SINGLE BUCKLE BRAIDED BELT          
                 
                15 rows selected
                 
                • 5. Re: lead function equivalent in Oracle to handle Clob datatype in xml
                  Kunal
                  Odie you are champ! amazing! this was perfect in generating the with data part but I am observing one strange issue. If i generate the product only till "AS product" part the i am able to see the sku_list1 with the child items but as soon as i generate till the part "AS productTranslation" the sku_list1 doesnt show up in the "as product". I am just not able understand why is this happening? one more thing does the usage of .getclobval() produces the clob part with escape chars like &lt; etc
                    WITH item_data AS
                      (select t.lvl
                             , t.item
                             , t.item_parent
                             , t.item_desc
                             , (
                                 select rtrim(
                                          xmlcast(
                                            xmlagg(xmlelement("e", c.item||','))
                                            as clob
                                          )
                                        , ','
                                        )
                                 from ct_atg_item_export c
                                 where c.item_parent = t.item
                                 and c.export_ind = 'N'
                               ) as sku_list1,
                              TO_CHAR(t.creationdate, 'MM/DD/YYYY HH:MI AM') creationDate,
                              t.dept,
                              t.class,
                              t.subclass,
                              t.diff_1,
                              t.diff_2,
                              t.diff_3,
                              t.standard_retail,
                              t.division,
                              t.season_id,
                              t.season_desc,
                              t.phase_id,
                              t.phase_desc,
                              t.item_sec_desc,
                              t.curr_phase
                        from ct_atg_item_export t
                        where t.export_ind = 'N'
                       )  
                     SELECT XMLElement("gsa-template",
                                    Xmlelement("import-items",
                        (SELECT XMLAGG(Xmlelement("add-item",
                                    XMLAttributes('product' AS "item-descriptor",
                                                  a.item AS "id"
                                                 ),
                                    XMLElement("set-property",
                                               XMLAttributes('displayNameDefault' AS "name" ),
                                               XMLCDATA(a.item_desc)
                                              ),
                                    XMLElement("set-property",
                                               XMLAttributes('longDescriptionDefault' AS "name" ),
                                               XMLCDATA(a.item_desc ||'-'||'webdisplay')
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('childSKUs' As "name" ),
                                               XMLCDATA(a.sku_list1)
                                              ).getstringval(),
                                    XMLElement("set-property",
                                               Xmlattributes('creationDate' As "name" ),
                                               XMLCDATA(a.creationDate)
                                               ),
                                    XMLElement("set-property",
                                              Xmlattributes('hazmat' As "name" ),
                                              XMLCDATA('false')
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('fabric' As "name" ),
                                               XMLCDATA('01=50,02=25,04=25')
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('translations' As "name" ),
                                               XMLCDATA('fr='||a.item||'_fr')
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('seasonId' As "name" ),
                                               XMLCDATA(a.season_id)
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('seasonDesc' As "name" ),
                                               XMLCDATA(a.season_desc)
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('phaseId' As "name" ),
                                               XMLCDATA(a.phase_id)
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('phaseDesc' As "name" ),
                                               XMLCDATA(a.phase_desc)
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('division' As "name" ),
                                               XMLCDATA(a.division)
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('department' As "name" ),
                                               XMLCDATA(a.dept)
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('class' AS "name" ),
                                               XMLCDATA(a.class)
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('subclass' AS "name" ),
                                               XMLCDATA(a.subclass)
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('sizeGroup' AS "name" ),
                                               XMLCDATA(a.diff_2)
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('nonMerchandise' AS "name" ),
                                               XMLCDATA('false')
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('nonReturnable' AS "name" ),
                                               XMLCDATA('false')
                                              ),
                                    XMLElement("set-property",
                                               Xmlattributes('taxable' AS "name" ),
                                               XMLCDATA('true')
                                              )
                                 )) AS product
                        FROM item_data a
                       where a.lvl = 1) -- )).getclobval() from dual --    if run till this part then sku_list1 is populated but is appended with the part below then sku_list1 is not displayed in xml
                  , (SELECT XMLAGG(Xmlelement("add-item",
                                     XMLAttributes('productTranslation' AS "item-descriptor",
                                                   b.item||'_fr' AS "id"
                                                  ),
                                     XMLElement("set-property",
                                                XMLAttributes('displayName' AS "name" ),
                                                XMLCDATA(b.item_sec_desc)
                                               ),
                                     XMLElement("set-property",
                                                XMLAttributes('longDescription' AS "name" ),
                                                XMLCDATA(b.item_sec_desc ||'-'||'webdisplay')
                                               )
                                   )) AS productTranslation
                        FROM item_data b
                       WHERE b.lvl=1))).getclobval() from dual;
                  • 6. Re: lead function equivalent in Oracle to handle Clob datatype in xml
                    odie_63
                    If i generate the product only till "AS product" part the i am able to see the sku_list1 with the child items but as soon as i generate till the part "AS productTranslation" the sku_list1 doesnt show up in the "as product". I am just not able understand why is this happening?
                    I can't explain it either. Looks like a bug when we use XMLCast as CLOB.

                    As a workaround, it seems to work when we use only one subquery (or no subquery at all) and XMLConcat on the different fragments :
                    WITH item_data AS
                        (select t.lvl
                               , t.item
                               , t.item_parent
                               , t.item_desc
                               , (
                                   select rtrim(
                                            xmlcast(
                                              xmlagg(xmlelement("e", c.item||','))
                                              as clob
                                            )
                                          , ','
                                          )
                                   from ct_atg_item_export c
                                   where c.item_parent = t.item
                                   and c.export_ind = 'N'
                                 ) as sku_list1,
                                TO_CHAR(t.creationdate, 'MM/DD/YYYY HH:MI AM') creationDate,
                                t.dept,
                                t.class,
                                t.subclass,
                                t.diff_1,
                                t.diff_2,
                                t.diff_3,
                                t.standard_retail,
                                t.division,
                                t.season_id,
                                t.season_desc,
                                t.phase_id,
                                t.phase_desc,
                                t.item_sec_desc,
                                t.curr_phase
                          from ct_atg_item_export t
                          where t.export_ind = 'N'
                         )  
                       SELECT XMLElement("gsa-template",
                               Xmlelement("import-items",
                          (SELECT XMLConcat(
                                  XMLAGG(
                                   Xmlelement("add-item",
                                      XMLAttributes('product' AS "item-descriptor",
                                                    a.item AS "id"
                                                   ),
                                      XMLElement("set-property",
                                                 XMLAttributes('displayNameDefault' AS "name" ),
                                                 XMLCDATA(a.item_desc)
                                                ),
                                      XMLElement("set-property",
                                                 XMLAttributes('longDescriptionDefault' AS "name" ),
                                                 XMLCDATA(a.item_desc ||'-'||'webdisplay')
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('childSKUs' As "name" ),
                                                 XMLCDATA(a.sku_list1)
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('creationDate' As "name" ),
                                                 XMLCDATA(a.creationDate)
                                                 ),
                                      XMLElement("set-property",
                                                Xmlattributes('hazmat' As "name" ),
                                                XMLCDATA('false')
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('fabric' As "name" ),
                                                 XMLCDATA('01=50,02=25,04=25')
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('translations' As "name" ),
                                                 XMLCDATA('fr='||a.item||'_fr')
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('seasonId' As "name" ),
                                                 XMLCDATA(a.season_id)
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('seasonDesc' As "name" ),
                                                 XMLCDATA(a.season_desc)
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('phaseId' As "name" ),
                                                 XMLCDATA(a.phase_id)
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('phaseDesc' As "name" ),
                                                 XMLCDATA(a.phase_desc)
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('division' As "name" ),
                                                 XMLCDATA(a.division)
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('department' As "name" ),
                                                 XMLCDATA(a.dept)
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('class' AS "name" ),
                                                 XMLCDATA(a.class)
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('subclass' AS "name" ),
                                                 XMLCDATA(a.subclass)
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('sizeGroup' AS "name" ),
                                                 XMLCDATA(a.diff_2)
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('nonMerchandise' AS "name" ),
                                                 XMLCDATA('false')
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('nonReturnable' AS "name" ),
                                                 XMLCDATA('false')
                                                ),
                                      XMLElement("set-property",
                                                 Xmlattributes('taxable' AS "name" ),
                                                 XMLCDATA('true')
                                                )
                                   ) 
                              ) --AS product
                            , XMLAGG(Xmlelement("add-item",
                                       XMLAttributes('productTranslation' AS "item-descriptor",
                                                     a.item||'_fr' AS "id"
                                                    ),
                                       XMLElement("set-property",
                                                  XMLAttributes('displayName' AS "name" ),
                                                  XMLCDATA(a.item_sec_desc)
                                                 ),
                                       XMLElement("set-property",
                                                  XMLAttributes('longDescription' AS "name" ),
                                                  XMLCDATA(a.item_sec_desc ||'-'||'webdisplay')
                                                 )
                                     )) --AS productTranslation
                              ) -- end of xmlconcat
                          FROM item_data a
                         where a.lvl = 1
                         ) 
                        )) 
                    from dual;
                    one more thing does the usage of .getclobval() produces the clob part with escape chars like &lt; etc
                    You left out a getstringval() call here :
                    XMLElement("set-property",
                                                 Xmlattributes('childSKUs' As "name" ),
                                                 XMLCDATA(a.sku_list1)
                                                ).getstringval(),
                    • 7. Re: lead function equivalent in Oracle to handle Clob datatype in xml
                      Kunal
                      Thanks a lot Odie. Its working flawlessly. Really appreciate your help!

                      Regards,
                      Kunal.