Thursday, July 23, 2015

ORA-22926 when using getClobVal to convert XMLType to CLOB

I ran into a problem the other day when moving some code from one database to another (both XE 11g). The code in question needs to convert an XMLType to a CLOB to do some (hacky) string manipulation on it, and then turn it back to an XMLType.

Here's the original code:

  l_xml := apex_web_service.make_request(...);
  -- little hack to remove bad empty namespace from result
  l_clob := l_xml.getClobVal();
  l_clob := replace (l_clob, '<mytag xmlns="">', '<mytag>');
  -- and then strip out bogus namespace to make parsing easier...
  l_clob := replace (l_clob, ' xmlns=""', '');
  l_xml := xmltype (l_clob);

The above had been working before, but now failed with "ORA-22926: specified trim length is greater than current LOB value's length".

Googling turned up a forum post which pointed out that getClobVal is deprecated, and the suggestion is to use XMLSerialize instead.

This can only be used in a SQL statement, not as a PL/SQL expression, so I had to rewrite as follows:

select xmlserialize(document l_xml as clob)
into l_clob
from dual;

And the error went away... (It seems some people have had problems even with the xmlserialize function, but that's quite an old post, so that particular bug could have been fixed a long time ago.)

No comments: