How to fix the Oracle error ORA-32766: instr with negative offset: use varchar semantics on LOBs?

In this post, you’ll learn more about the Oracle ErrorORA-32766: instr with negative offset: use varchar semantics on LOBs with the details on why you receive this error and the possible solution to fix it.

Oracle Error Description

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

Reason for the Error ORA-32766: instr with negative offset: use varchar semantics on LOBs

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.

How to fix the Error ORA-32766: instr with negative offset: use varchar semantics on LOBs ?

You can fix this error in Oracle by following the below steps

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.