Issue

When using instr(nclob,), if the offset becomes large, the result value is output incorrectly, causing DBMS_LOB.INSTR to return abnormal values.

-- Issue Scenario
1. Oracle 19c
SQL> 
-- drop table clobtest purge;
create table clobtest (v_long_text clob);

-- Insert CLOB of 40960 length characters + 'lobloblobloblob'
SQL>
insert into clobtest 
select to_clob(dbms_random.string('U', 4000))||
to_clob(dbms_random.string('U', 4000))||
to_clob(dbms_random.string('U', 4000))||
to_clob(dbms_random.string('U', 4000))||
to_clob(dbms_random.string('U', 4000))||
to_clob(dbms_random.string('U', 4000))||
to_clob(dbms_random.string('U', 4000))||
to_clob(dbms_random.string('U', 4000))||
to_clob(dbms_random.string('U', 4000))||
to_clob(dbms_random.string('U', 4000))||
to_clob(dbms_random.string('U', 960))||
'lobloblobloblob' from dual;
commit;

SQL> 
select length(v_long_text) from clobtest;

LENGTH(V_LONG_TEXT)
-------------------
            40975

SQL>
select 
DBMS_LOB.INSTR(v_long_text, 'lob', 1, 1) result1,
DBMS_LOB.INSTR(v_long_text, 'lob', 1, 2) result2,
DBMS_LOB.INSTR(v_long_text, 'lob', 1, 3) result3,
DBMS_LOB.INSTR(v_long_text, 'lob', 1, 4) result4,
DBMS_LOB.INSTR(v_long_text, 'lob', 1, 5) result5,
DBMS_LOB.INSTR(v_long_text, 'lob', 40960, 1) result6
from clobtest
;

 RESULT1    RESULT2    RESULT3    RESULT4    RESULT5    RESULT6
---------- ---------- ---------- ---------- ---------- ----------
   40961      40964      40967      40970      40973      40961



2. Tibero 6
-- drop table clobtest purge;
create table clobtest (v_long_text clob);

-- Insert CLOB of 40960 length characters + 'lobloblobloblob'
insert into clobtest
select to_clob(dbms_random.string('U', 20000))||to_clob(dbms_random.string('U', 20960))||'lobloblobloblob' from dual;
commit;

SQL> 
select length(v_long_text) from clobtest;

LENGTH(V_LONG_TEXT)
-------------------
            40975


select 
DBMS_LOB.INSTR(v_long_text, 'lob', 1, 1) result1,
DBMS_LOB.INSTR(v_long_text, 'lob', 1, 2) result2,
DBMS_LOB.INSTR(v_long_text, 'lob', 1, 3) result3,
DBMS_LOB.INSTR(v_long_text, 'lob', 1, 4) result4,
DBMS_LOB.INSTR(v_long_text, 'lob', 1, 5) result5,
DBMS_LOB.INSTR(v_long_text, 'lob', 40960, 1) result6
from clobtest
;

 RESULT1    RESULT2    RESULT3    RESULT4    RESULT5    RESULT6
---------- ---------- ---------- ---------- ---------- ----------
   40963      40966      40969      40972      40975      32769.

The issue is resolved by implementing the logic identically to the clob logic.