0

I have to update a column with a xml of 252000 char. I have tried this How to insert/update larger size of data in the Oracle tables? but i still got error 'string literal too long'.

I want to write some code with DBMS_LOB package like this:

declare
  c1 clob;
  c2 varchar2(32000);
begin
  c1 := 'abc';
  c2 := 'text, which contains 32 000 characters';
  dbms_lob.append(c1, c2);
  c2 := 'some more text, which contains 32 000 characters';
  dbms_lob.append(c1, c2);
  c2 := 'some more text, which contains 32 000 characters';
  dbms_lob.append(c1, c2);
  c2 := 'some more text, which contains 32 000 characters';
  dbms_lob.append(c1, c2);
  c2 := 'some more text, which contains 32 000 characters';
  dbms_lob.append(c1, c2);
  c2 := 'some more text, which contains 32 000 characters';
  dbms_lob.append(c1, c2);
  c2 := 'some more text, which contains 32 000 characters';
  dbms_lob.append(c1, c2);
  c2 := 'last text, which contains 27 372 characters';
  dbms_lob.append(c1, c2);
  update table set d1= c1 where d2id=12;
end;

Are there any other way write the query, where I can put whole my xml in one?

shA.t
  • 16,580
  • 5
  • 54
  • 111
LogiL
  • 1
  • 1
  • are you sure, the issue is when you try to update the table and not before. are there any characters apart from english characters in your XML, then c2 will not be able to store 32000 characters. because some characters are of 2 bytes and not of 1 bytes.. did you try declaring c2 as clob ? – Sudipta Mondal Sep 11 '17 at 03:40
  • Use XML Datatype which will not validate XML Data Size. – Yogesh Sharma Sep 11 '17 at 07:55

0 Answers0