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

The following is the cause of this error:

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

Action you can take to resolve this issue: 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.

Tags: Database, Microsoft, Oracle, SQL, Software, DBMS, Error Messages, Directory

Related