dbms_lob.substr weirdness with clobs

Hi,

I am encountering some very strange behaviour trying to manipulate CLOB data in Oracle9i, and hoped someone here might have already found a solution.

I am working with data that is around 20k-50k in size, and want to break it up into smaller CLOB chunks. I can select the CLOB data fine, and I have checked the math, as well as doing some manual testing in SQL to verify what I am doing, and as far as I can tell, it should work.

The problem is that when I try to select a portion of the CLOB data using the dbms_lob.substr function, it returns most, but not all of the specified information, even though the source CLOB has much more than is being selected.

Here is my process:

Locate start position using dbms_lob.instr():

x := dbms_lob.instr(src_clob,'<TAG>') + 5;

This give me the correct starting byte position within the source CLOB, using SQL*Plus and real data in the database in testing.

Next, I locate the end position using dbms_lob.instr() again, and calculate the size of the data I am extracting:

y := dbms_lob.instr(src_clob,'<TAG2>') - 1 - x; if y <= 0 then

    y := dbms_lob.getlength(src_clob) - x; end if;

Again, I have verified this value using real data, and SQL*Plus, and have proven it is correct.

Next, I extract the CLOB substring using:

dest_clob := dbms_lob.substr(src_clob, y, x);

Then, I update the value in the database using:

select src_clob into tmp_clob
from table_name
where primarykey = ID
for update;

update table_name
set src_clob = dest_clob
where primarykey = ID;

This too seems to work fine, but when I query the src_clob, I find that most of the data (around 16k in my test case) is there, starting at the correct position, but the end of the text is around 200 bytes short of the position it is supposed to be at!

What gives? Is this a bug? Am I missing something?

Any thoughts or suggestions would be appreciated.

Thanks in advance!

Bjorn Received on Tue Dec 25 2001 - 23:18:38 CST