This discussion is archived
7 Replies Latest reply: Dec 16, 2012 5:09 PM by Kunal RSS

lead function equivalent in Oracle to handle Clob datatype in xml

Kunal Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot Odie. Its working flawlessly. Really appreciate your help!

    Regards,
    Kunal.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points