Hi Tom,

I am trying to insert a xml that is extracted from another Clob column into clob column of a temporary table.

The Length of the extracted xml is around 8000.

i also saw your post related to this
https://asktom.oracle.com/pls/apex/f?p=100:11:107122159008174::NO:::

what i did is :-

I create a table

Create Table CM_TBL (id varchar2(10), data_area clob);

then function inserting extracted data into CM_tbl

SELECT
'1' id
(EXTRACT(VALUE(P), '/abcList/*')) AS cm_xml,
FROM
test_tbl FACT1
TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<root>' ||FACT1.test_data_area||'</root>'), '//root/xyz/abcList'))) P;

The Extracted XML is around 8000 in length.

Now when i trying to insert it into CM_tbl i am getting error

insert into cm_tbl
SELECT
'1' id
to_clob(EXTRACT(VALUE(P), '/abcList/*')) AS cm_xml,
FROM
test_tbl FACT1
TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<root>' ||FACT1.test_data_area||'</root>'), '//root/xyz/abcList'))) P;

SQL Error: ORA-19011: Character string buffer too small
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

I also tried creating procedure and inserting data through it, that also dint worked.

Please Help!!

Thanks in Advance

SQL> create table t (xml_doc clob);

Table created.

SQL> create table cm_tbl ( id varchar2 ( 10 ) , data_area clob ) ;

Table created.

SQL>
SQL> declare
  2    xml clob;
  3  begin
  4    xml := '<root><xyz><abcList>' ||
  5             lpad('x', 8000, 'x') || '</abcList></xyz></root>';
  6
  7    insert into t values (xml);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> insert into cm_tbl
  2  select '1' id, to_clob(extract(value ( p ), '/abcList/text()')) as cm_xml
  3  from  t fact1, table ( xmlsequence ( extract ( xmltype ( '<root>' ||
  4    fact1.xml_doc ||
  5    '</root>' ) , '//root/xyz/abcList' ) ) ) p;
insert into cm_tbl
*
ERROR at line 1:
ORA-19011: Character string buffer too small


SQL>
SQL> insert into cm_tbl
  2  select '1' id, extract(value ( p ), '/abcList/text()').getClobVal() as cm_xml
  3  from  t fact1, table ( xmlsequence ( extract ( xmltype ( '<root>' ||
  4    fact1.xml_doc ||
  5    '</root>' ) , '//root/xyz/abcList' ) ) ) p;

1 row created.