Oracle Database Discussions

Hi there

--create test table
create table test_vatchar2_clob (v2 varchar2 (4000), c clob);
--pl/sql block
declare
 v_clob clob;
 v_xmltype xmltype;
 v_varchar2 varchar2(32000);
begin
 v_clob:='<Root>';
 
 for i in 1..2000 loop
   v_clob:=v_clob||'<c>'||I||'</c>';
 end loop;
 
 v_clob:=v_clob||'</Root>';
 
 select xmltype(v_clob) into v_xmltype from dual;
 delete test_vatchar2_clob;
 
 insert into test_vatchar2_clob(v2, c)
 select v2,c 
        FROM   (SELECT v_xmltype Xm
             FROM   dual) a,
            Xmltable('Root' Passing a.Xm Columns c clob Path
                     'string-join(c,";")',
                     v2 varchar2(4000) Path
                     'substring(string-join(c,";"),1,4000)'
                     );
commit;                      
end;

--execute and get error ORA-01706

--change this

'substring(string-join(c,";"),1,4000)'

for

'substring(string-join(c,";"),1,3999)'

and it works

What happens to one character?