Wednesday, April 20, 2011

Oracle Bug

One of my developers just noticed a strange behavior in all versions of Oracle 10g that we could test (10.2.0.3 on Win32, 10.2.0.5 on Win64).


Here is a simplified case:
REPLACE command is fired against a CLOB variable where we are trying to change 'T_' to 'ZZ'. In the CLOB there is a part of text that looks like 'T('.

To my own surprise, Oracle replaces both 'T_' and 'T(' with 'ZZ': it looks like underscore is being interpreted as a wild-card! By the way, this behavior is completely gone in 11gR2.

Code sample:
declare
    v1_cl CLOB := 'T_T(a number)';
begin
    v1_cl := replace (v1_cl, 'T_', 'ZZ');
    dbms_output.put_line(v1_cl);
end;

Received result: ZZZZa number)
Expected result: ZZT(a number)


Update (thanks to Grigoriy Novikov): It is not a feature - it is a real Oracle bug (Metalink Bug ID:4598943), first detected in 9.2.0.6 and fixed in 11g. So, please, be aware of it!

1 comment:

Grigoriy said...

Finally I found it on Metalink - Bug 4598943
Database Version - 9.2.0.6
Fixed in Product Version- 11.0.0.0.0
Workaround: None