Skip to main content

Search

Welcome to Tibero GTS!

Abnormal Output of DBMS_LOB.INSTR Result

Document Type | Troubleshooting

Category | App Development

Applicable Product Versions | 6FS06, 6FS07, 6FS07PS

Document Number | TDETS006

 

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.

 

 

Cause

This issue occurred due to incorrect implementation of the nclob logic in instr(nclob, ).
 
 
 
 

Solutions

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

 

 

 

 

Related to