As the title of this blog entry indicates, I encountered a problem yesterday with my CLOBs somehow ending up being truncated. I scratched my head, poured over the documentation and still came up blank.
I felt sure the problem was something to do with the multibyte character set my database (10.2.0.4) was set up with, but everything in the documentation indicated that DBMS_LOB subprograms worked in terms of characters, not bytes, when CLOBs were involved.
I managed to recreate my issue:
declare
l_finalSQL VARCHAR2(32767 char);
l_innerSQL CLOB := 'a';
l_outerSQL CLOB := 'b';
l_userid INT;
gOutersql varchar2(113) := 'ccccccccccccccccccccccccccccccccccccccccc'||
'ccccccccccccccccccccccccccccccccccccccc'||
'ccccccccccccccccccccccccccccccccc';
begin
for i in 1..1342
loop
l_outersql := l_outersql||'b';
end loop;
dbms_output.put_line('l_outersql = '||length(l_outersql));
for i in 1..10860
loop
l_innersql := l_innersql||'a';
end loop;
dbms_output.put_line('l_innersql = '||length(l_innersql));
l_userid := 123;
dbms_output.put_line('l_userid = '||length(l_userid));
l_outerSQL := l_outerSQL||' FROM ('||l_innerSQL||gOUTERSQL||
TO_CHAR(l_userid);
dbms_output.put_line('appended l_outersql = '||
dbms_lob.getlength(l_outersql));
IF dbms_lob.getlength(l_outerSQL) <= 32767 THEN
l_finalSQL := dbms_lob.substr(l_outerSQL, 32767, 1);
else raise too_many_rows;
end if;
dbms_output.put_line('l_finalsql = '||length(l_finalsql));
end;
/
which produced the following output:
l_outersql = 1343 l_innersql = 10861 l_userid = 3 appended l_outersql = 12327 l_finalsql = 10922
Note the 12327 vs 10922 – the expected and actual lengths of the l_finalSQL string.
I could not spot what the problem was for the life of me, so I asked for help on my work’s Oracle chat channel (don’t think I didn’t have a chuckle about that given my previous blog entry!). Thankfully, someone else had the presence of mind to check My Oracle Support, and it turned out that I have most likely tripped over bug 9020537. This isn’t fixed until 12.1 (!) or 11.2.0.2 (Server Patch Set) or 10.2.0.5.3 (Patch Set Update).
Unfortunately, upgrading to any of these versions is not an option for us in the timescale I needed my code to be working, and there is no workaround (boo!) so I’ve had to rewrite my code to work around the issue. Annoying! (Even more so when what I’m doing is to work around the fact that EXECUTE IMMEDIATE doesn’t handle CLOBs until 11g…)
Recent Comments