Oracle Error ORA-32766: instr with negative offset: use varchar semantics on LOBs

Oracle Error Message

ORA-32766: instr with negative offset: use varchar semantics on LOBs

Reason for the Error

The current varchar behavior is different. e.g. instr(‘abcd’, ‘cd’, -2, 1) returns 3, whereas instr(to_clob(‘abcd’), ‘cd’, -2, 1) returns 0, (i.e. no match), because the reverse search starts from offset -2, which points to ‘c’ and moving backward, i.e. ‘d’ is ignored. This is symmetric to instr(‘dcba’,’dc’,2,1), which returns 0.

Solution

ORACLE uses the same varchar semantics on LOBs (instr). Using the same example, instr(to_clob(‘abcd’), ‘cd’, -2, 1) will return 3 as in the varchar case.