현상

instr(nclob,) 일 때 offset이 길어지면 결과 값이 잘못 나오는 현상으로, DBMS_LOB.INSTR 결과가 비정상적인 값으로 출력됩니다.

--이슈 시나리오
1. Oracle 19c
SQL> 
-- drop table clobtest purge;
create table clobtest (v_long_text clob);

-- 40960 길이의 문자 + 'lobloblobloblob' 의 CLOB insert
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);

-- 40960 길이의 문자 + 'lobloblobloblob' 의 CLOB insert
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.

clob 로직과 동일하게 구현하여 해결합니다.