--sql query
select xmlagg(xmlconcat(xmlelement("empno",empno), xmlelement("ename",ename))) 
from scott.emp where rownum<3;
--result
<empno>7369</empno><ename>SMITH</ename><empno>7499</empno><ename>ALLEN</ename>


--Expected Result

--yytet00pubSubBoundary00tetyy
Content-ID: xx.7499@xx.com
Content-Length: 78

<?xml version="1.0" encoding="UTF-8"?>
<empno>7369</empno><ename>SMITH</ename>

--yytet00pubSubBoundary00tetyy
Content-ID: xx.7499@xx.com
Content-Length: 78

<?xml version="1.0" encoding="UTF-8"?>
<empno>7499</empno><ename>ALLEN</ename>

--yytet00pubSubBoundary00tetyy

SQL> set sqlblanklines on
SQL> with 
  2    lines as (
  3      select '
  4  --yytet00pubSubBoundary00tetyy
  5  Content-ID: xx.#ID@xx.com
  6  Content-Length: #LG
  7  
  8  <?xml version="1.0" encoding="UTF-8"?>
  9  ' lines
 10      from dual
 11    )
 12  select replace(replace(lines, '#ID', empno), '#LG', length(empdata))||empdata res
 13  from lines, 
 14        (select empno, xmlconcat(xmlelement("empno",empno), xmlelement("ename",ename)) empdata
 15         from emp
 16         where rownum < 3)
 17  /
RES
-------------------------------------------------------------------------------------------------

--yytet00pubSubBoundary00tetyy
Content-ID: xx.7369@xx.com
Content-Length: 39

<?xml version="1.0" encoding="UTF-8"?>
<empno>7369</empno><ename>SMITH</ename>

--yytet00pubSubBoundary00tetyy
Content-ID: xx.7499@xx.com
Content-Length: 39

<?xml version="1.0" encoding="UTF-8"?>
<empno>7499</empno><ename>ALLEN</ename>

2 rows selected.

SQL> with
  2    lines as (
  3      select '
  4  --yytet00pubSubBoundary00tetyy
  5  Content-ID: xx.#ID@xx.com
  6  Content-Length: #LG
  7
  8  <?xml version="1.0" encoding="UTF-8"?>
  9  ' lines
 10      from dual
 11    )
 12  select listagg(to_clob(replace(replace(lines, '#ID', empno), '#LG', length(empdata))||empdata))
 13            within group (order by empno) res
 14  from lines,
 15        (select empno, xmlconcat(xmlelement("empno",empno), xmlelement("ename",ename)) empdata
 16         from emp
 17         where rownum < 3)
 18  /
RES
-----------------------------------------------------------------------------------------------------

--yytet00pubSubBoundary00tetyy
Content-ID: xx.7369@xx.com
Content-Length: 39

<?xml version="1.0" encoding="UTF-8"?>
<empno>7369</empno><ename>SMITH</ename>
--yytet00pubSubBoundary00tetyy
Content-ID: xx.7499@xx.com
Content-Length: 39

<?xml version="1.0" encoding="UTF-8"?>
<empno>7499</empno><ename>ALLEN</ename>

1 row selected.

SQL> set sqlblanklines on
SQL> with
  2    lines as (
  3      select '
  4  --yytet00pubSubBoundary00tetyy
  5  Content-ID: xx.#ID@xx.com
  6  Content-Length: #LG
  7
  8  <?xml version="1.0" encoding="UTF-8"?>
  9  ' lines
 10      from dual
 11    )
 12  select listagg(to_clob(replace(replace(lines, '#ID', empno), '#LG', length(empdata))||empdata),'
 13                 ') within group (order by empno) res
 14  from lines,
 15        (select empno,
 16                xmlelement("employee",xmlconcat(xmlelement("empno",empno), xmlelement("ename",ename))) empdata
 17         from emp
 18         where rownum < 3)
 19  /
RES
-----------------------------------------------------------------------------------------------------------------

--yytet00pubSubBoundary00tetyy
Content-ID: xx.7369@xx.com
Content-Length: 60

<?xml version="1.0" encoding="UTF-8"?>
<employee><empno>7369</empno><ename>SMITH</ename></employee>

--yytet00pubSubBoundary00tetyy
Content-ID: xx.7499@xx.com
Content-Length: 60

<?xml version="1.0" encoding="UTF-8"?>
<employee><empno>7499</empno><ename>ALLEN</ename></employee>

1 row selected.

19011, 00000, "Character string buffer too small" 
// *Cause:  The string result asked for is too big to return back
// *Action:  Get the result as a lob instead

create table xx_test1c(xxml clob);
/
SET serveroutput ON;
/
DECLARE
  xxml CLOB;
BEGIN
  --set sqlblanklines on
WITH lines AS
  (SELECT '            

--yytet00pubSubBoundary00tetyy    
Content-ID: PIM.82.#ID@master.com    
Content-Type: application/x.xxl.pimdata+xml    
Content-Length: #LG      

<?xml version="1.0" encoding="UTF-8"?>    
' lines
  FROM dual
  )
SELECT listagg(to_clob(REPLACE(REPLACE(lines, '#ID',item_number), '#LG', LENGTH(proddata))
  ||proddata)) within GROUP (
ORDER BY item_number)
INTO xxml
FROM lines,
  (SELECT '10' item_number,
    xmlagg(xmlconcat(XMLELEMENT("SyncItemPrimaryAttribute", XMLELEMENT("ItemIdentification", XMLELEMENT("Identification", xmlforest(msiv.inventory_item_id AS "ItemID", msiv.organization_id AS "OrgID", orgdef.organization_code AS "OrgCode", orgdef.organization_name AS "OrgName", kfv.concatenated_segments AS "ItemNumber", msiv.description AS "Description") )), XMLELEMENT("ItemBase", XMLforest(mic.segment1 AS "Catalog", NULL AS "Spoke", NULL AS "PubSeqId", msiv.long_description AS "LongDescription", lc.element_number AS "LifeCycle", lp.element_number AS "LifeCyclePhase",msiv.approval_status AS "ApprovalStatus", msiv.inventory_item_status_code AS "Status", msiv.item_type AS "TypeCode", msiv.eng_item_flag AS "EngineeringItemFlag", msiv.primary_uom_code AS "BaseUOMCode", msiv.secondary_uom_code AS "SecondaryUOMCode", msiv.creation_date "CreationDate", msiv.last_update_date AS "LastUpdateDate" ))),
    (SELECT xmlagg(xmlconcat(XMLELEMENT("AttributeGroup",XMLELEMENT("ID",eag.attr_group_id),XMLELEMENT("Name",eag.attr_group_name) ,
      (SELECT xmlagg(XMLELEMENT("Attribute", XMLFOREST(attr_val.attribute_name AS "Name",DECODE(attr_val.data_type_code,'C',attr_val.attribute_char_value,'N',TO_CHAR (attr_val.attribute_number_value),'X',attr_val.attribute_date_value) AS "Value")))
      FROM ego_all_attr_base_v attr_val
      WHERE 1                   =1
      AND attr_val.extension_id = ems.extension_id
      )) ))
    FROM ego_attr_groups_v eag,
      ego_mtl_sy_items_ext_b ems
    WHERE ems.attr_group_id    = eag.attr_group_id
    AND eag.attr_group_type    = 'EGO_ITEMMGMT_GROUP'
    AND msiv.inventory_item_id = ems.inventory_item_id
    AND ems.organization_id    = MSIV.ORGANIZATION_ID
    ))) proddata
  FROM mtl_system_items_vl msiv,
    org_organization_definitions orgdef,
    mtl_system_items_b_kfv kfv,
    mtl_item_catalog_groups_b mic,
    pa_ego_lifecycles_v lc,
    pa_ego_phases_v lp
  WHERE msiv.inventory_item_id   = kfv.inventory_item_id
  AND msiv.item_catalog_group_id = mic.item_catalog_group_id
  AND msiv.lifecycle_id          = lc.proj_element_id
  AND Msiv.Current_Phase_Id      = Lp.Proj_Element_Id
  AND msiv.organization_id       = kfv.organization_id
  AND msiv.organization_id       = orgdef.organization_id
  AND MSIV.ORGANIZATION_ID       = 82
  AND rownum                     <20
  );
INSERT INTO xx_test1c VALUES
  (xxml
  ) ;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line(sqlerrm);
END;

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((replace(replace(lines, '#ID', empno), '#LG', length(empdata))||empdata).getClobVal())
             within group (order by empno) res into xxml 
   from lines,
         (select empno, xmlelement("employee",xmlconcat(xmlelement("empno",empno), xmlelement("ename",ename))) empdata
          from scott.emp
          where rownum < 10);
          
insert into xx_test1c values(xxml) ;
commit;
  exception
  when others then dbms_output.put_line(sqlerrm);
  end;
/

Error report -
ORA-06550: line 18, column 92:
PL/SQL: ORA-22806: not an object or REF
ORA-06550: line 5, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

INSERT INTO xx_test1c (<the query>);

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))).getClobVal() 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;

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

with e as (
            select  empno,
                    xmlelement(
                               "employee",xmlconcat(
                                                    xmlelement("empno",empno),
                                                    xmlelement("ename",ename)
                                                   )
                              ).getclobval() empdata,
                    row_number() over(order by empno) rn,
                    count(*) over() cnt
              from  emp
           ),
     r(
       res,
       rn,
       cnt
      ) as (
             select  '
        
    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#' || empno || '@xx.com
    Content-Length: ' || length(empdata) || '
  
    <?xml version="1.0" encoding="UTF-8"?>' || empdata res,
                     rn,
                     cnt
               from  e
               where rn = 1
            union all
             select  r.res || '
        
    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#' || empno || '@xx.com
    Content-Length: ' || length(empdata) || '
  
    <?xml version="1.0" encoding="UTF-8"?>' || e.empdata res,
                     e.rn rn,
                     r.cnt cnr
               from  r,
                     e
               where e.rn = r.rn + 1
           )
select  res
  from  r
  where rn = cnt
/

RES
---------------------------------------------------------------------------------------------------------


    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7369@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7369</empno><ename>SMITH</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7499@xx.com
    Content-Length: 60

RES
---------------------------------------------------------------------------------------------------------

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7499</empno><ename>ALLEN</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7521@xx.com
    Content-Length: 59

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7521</empno><ename>WARD</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7566@xx.com

RES
---------------------------------------------------------------------------------------------------------
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7566</empno><ename>JONES</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7654@xx.com
    Content-Length: 61

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7654</empno><ename>MARTIN</ename></employee>

    --yytet00pubSubBoundary00tetyy

RES
---------------------------------------------------------------------------------------------------------
    Content-ID: xx.#7698@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7698</empno><ename>BLAKE</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7782@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7782</empno><ename>CLARK</ename></employee>


RES
---------------------------------------------------------------------------------------------------------
    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7788@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7788</empno><ename>SCOTT</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7839@xx.com
    Content-Length: 59

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7839</empno><ename>KING</ename></employee>

RES
---------------------------------------------------------------------------------------------------------

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7844@xx.com
    Content-Length: 61

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7844</empno><ename>TURNER</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7876@xx.com
    Content-Length: 60


RES
---------------------------------------------------------------------------------------------------------
    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7876</empno><ename>ADAMS</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7900@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7900</empno><ename>JAMES</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7902@xx.com
    Content-Length: 59

RES
---------------------------------------------------------------------------------------------------------

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7902</empno><ename>FORD</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7934@xx.com
    Content-Length: 61

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7934</empno><ename>MILLER</ename></employee>


SQL> 

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))).getClobVal() 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;

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

INSERT INTO xx_test1c
with e as (
            select  empno,
                    xmlelement(
                               "employee",xmlconcat(
                                                    xmlelement("empno",empno),
                                                    xmlelement("ename",ename)
                                                   )
                              ).getclobval() empdata,
                    row_number() over(order by empno) rn,
                    count(*) over() cnt
              from  emp
           ),
     r(
       res,
       rn,
       cnt
      ) as (
             select  '
        
    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#' || empno || '@xx.com
    Content-Length: ' || length(empdata) || '
  
    <?xml version="1.0" encoding="UTF-8"?>' || empdata res,
                     rn,
                     cnt
               from  e
               where rn = 1
            union all
             select  r.res || '
        
    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#' || empno || '@xx.com
    Content-Length: ' || length(empdata) || '
  
    <?xml version="1.0" encoding="UTF-8"?>' || e.empdata res,
                     e.rn rn,
                     r.cnt cnr
               from  r,
                     e
               where e.rn = r.rn + 1
           )
select  res
  from  r
  where rn = cnt
/

SQL> create table xx_test1c(res clob)
  2  /

Table created.

SQL> INSERT INTO xx_test1c
  2  with e as (
  3              select  empno,
  4                      xmlelement(
  5                                 "employee",xmlconcat(
  6                                                      xmlelement("empno",empno),
  7                                                      xmlelement("ename",ename)
  8                                                     )
  9                                ).getclobval() empdata,
 10                      row_number() over(order by empno) rn,
 11                      count(*) over() cnt
 12                from  emp
 13             ),
 14       r(
 15         res,
 16         rn,
 17         cnt
 18        ) as (
 19               select  '
 20          
 21      --yytet00pubSubBoundary00tetyy
 22      Content-ID: xx.#' || empno || '@xx.com
 23      Content-Length: ' || length(empdata) || '
 24    
 25      <?xml version="1.0" encoding="UTF-8"?>' || empdata res,
 26                       rn,
 27                       cnt
 28                 from  e
 29                 where rn = 1
 30              union all
 31               select  r.res || '
 32          
 33      --yytet00pubSubBoundary00tetyy
 34      Content-ID: xx.#' || empno || '@xx.com
 35      Content-Length: ' || length(empdata) || '
 36    
 37      <?xml version="1.0" encoding="UTF-8"?>' || e.empdata res,
 38                       e.rn rn,
 39                       r.cnt cnr
 40                 from  r,
 41                       e
 42                 where e.rn = r.rn + 1
 43             )
 44  select  res
 45    from  r
 46    where rn = cnt
 47  /

1 row created.

SQL> set pagesize 50000
SQL> set long 50000
SQL> select  count(*)
  2    from  xx_test1c
  3  /

  COUNT(*)
----------
         1

SQL> select  *
  2    from  xx_test1c
  3  /

RES
-------------------------------------------------------------------------------------------------------------


    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7369@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7369</empno><ename>SMITH</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7499@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7499</empno><ename>ALLEN</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7521@xx.com
    Content-Length: 59

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7521</empno><ename>WARD</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7566@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7566</empno><ename>JONES</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7654@xx.com
    Content-Length: 61

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7654</empno><ename>MARTIN</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7698@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7698</empno><ename>BLAKE</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7782@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7782</empno><ename>CLARK</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7788@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7788</empno><ename>SCOTT</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7839@xx.com
    Content-Length: 59

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7839</empno><ename>KING</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7844@xx.com
    Content-Length: 61

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7844</empno><ename>TURNER</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7876@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7876</empno><ename>ADAMS</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7900@xx.com
    Content-Length: 60

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7900</empno><ename>JAMES</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7902@xx.com
    Content-Length: 59

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7902</empno><ename>FORD</ename></employee>

    --yytet00pubSubBoundary00tetyy
    Content-ID: xx.#7934@xx.com
    Content-Length: 61

    <?xml version="1.0" encoding="UTF-8"?><employee><empno>7934</empno><ename>MILLER</ename></employee>


SQL> 

SQL Error: ORA-00600: internal error code, arguments: [qctfrc : bfc], [4000], [0], [1], [873], [1], [2], [394], [], [], [], []
00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause:    This is the generic internal error number for Oracle program
           exceptions.	This indicates that a process has encountered an
           exceptional condition.
*Action:   Report as a bug - the first argument is the internal error number

SQL> select * from v$version
  2  /

BANNER
-----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL>