1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Facing issue while generating xmldata with boundaries

Discussion in 'SQL PL/SQL' started by wantmannu, Jan 29, 2015.

  1. wantmannu

    wantmannu Guest

    Hi,
    I am facing issue while running below script.
    Code (Text):

    SET serveroutput ON;
    /
    DECLARE
      xxml CLOB;
    BEGIN
      --set sqlblanklines on
    WITH lines AS
      (SELECT '            

    --yytet00pubSubBoundary00tetyy    
    Content-ID: xx.#ID@xx.com    
    Content-Length: #LG      

    <?xml version="1.0" encoding="UTF-8"?>    
    ' lines
      FROM dual
      )
    SELECT listagg(to_clob(REPLACE(REPLACE(lines, '#ID', segment1), '#LG', LENGTH(proddata)))
      ||proddata) within GROUP (
    ORDER BY segment1) res
    INTO xxml
    FROM lines,
      (SELECT segment1,
        xmlelement("products",xmlconcat(xmlelement("itemnumber",segment1), xmlelement("itemtype",item_type))) proddata
      FROM mtl_system_items_b
      WHERE rownum < 1000
      );
    INSERT INTO xx_test1c VALUES
      (xxml
      ) ;
    COMMIT;
    EXCEPTION
    WHEN OTHERS THEN
      dbms_output.put_line(sqlerrm);
    END;
     
    Error is
    Code (Text):

    anonymous block completed
    ORA-01489: result of string concatenation is too long