현상
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.
SQL
복사
원인
instr(nclob, )의 nclob 로직 구현이 잘못되어 발생했습니다.
해결
clob 로직과 동일하게 구현하여 해결합니다.