Creating a Text Index with USER_DATASTORE Shows Error DRG-50857, ORA-22920 in CTX_USER_INDEX_ERRORS (Doc ID 1110326.1)

Last updated on DECEMBER 17, 2011

Applies to:

Oracle Text - Version: 10.2.0.1 to 11.2.0.3 - Release: 10.2 to 11.2
Information in this document applies to any platform.

Symptoms

Creating a domain index with a user_datastore procedure that returns a CLOB fails to index the document with the following errors:

DRG-50857: oracle error in drsinopen
ORA-22920: row containing the LOB value is not locked

When using the FOR UPDATE clause to lock the CLOB, indexing succeeds, but the CLOB in the table disappears (seems to be an empty CLOB).

create table tst_tab
( id number not null constraint tst_tab_pk primary key
, text_column clob not null
, dummy_01 char(1) default 'X' not null
, dummy_02 char(1) default 'X' not null
, dummy_03 char(1) default 'X' not null
);

insert into tst_tab (id, text_column) select level, to_char(to_date(level,'J'),'JSP') from dual connect by level <= 100;

commit;

create or replace procedure tst_feed_01(rid in rowid, dataout in out nocopy clob) is
begin
-- This will return the first 4000 characters, so not a real work around
select substr(text_column,1,power(2,16)-1)
into dataout
from tst_tab
where rowid = rid
;
-- exception when NO_DATA_FOUND then dataout := null;
end;
/


create or replace procedure tst_feed_02(rid in rowid, dataout in out nocopy clob) is
begin
-- This result in error reading document (ORA-22920), except the first record
select text_column
into dataout
from tst_tab
where rowid = rid
;
-- exception when NO_DATA_FOUND then dataout := null;
end;
/

create or replace procedure tst_feed_03(rid in rowid, dataout in out nocopy clob) is
begin
-- This will delete the clob, except the last record
select text_column
into dataout
from tst_tab
where rowid = rid
for update
;
-- exception when NO_DATA_FOUND then dataout := null;
end;
/

begin
ctx_ddl.create_preference('tst_uds_01','user_datastore');
ctx_ddl.set_attribute('tst_uds_01', 'procedure', 'tst_feed_01');
ctx_ddl.set_attribute('tst_uds_01', 'output_type', 'CLOB');
end;
/

begin
ctx_ddl.create_preference('tst_uds_02','user_datastore');
ctx_ddl.set_attribute('tst_uds_02', 'procedure', 'tst_feed_02');
ctx_ddl.set_attribute('tst_uds_02', 'output_type', 'CLOB');
end;
/
begin
ctx_ddl.create_preference('tst_uds_03','user_datastore');
ctx_ddl.set_attribute('tst_uds_03', 'procedure', 'tst_feed_03');
ctx_ddl.set_attribute('tst_uds_03', 'output_type', 'CLOB');
end;
/

create index tst_ctx_01
on tst_tab (dummy_01)
indextype is ctxsys.context
parameters('datastore tst_uds_01')
;

Index created.

create index tst_ctx_02
on tst_tab (dummy_02)
indextype is ctxsys.context
parameters('datastore tst_uds_02')
;

Index created.

create index tst_ctx_03
on tst_tab (dummy_03)
indextype is ctxsys.context
parameters('datastore tst_uds_03')
;

Index created.

select err_index_name
, err_text
, count(*)
from ctxsys.ctx_index_errors
where err_index_owner = 'TEXT_USER'
and err_index_name in ('TST_CTX_01', 'TST_CTX_02', 'TST_CTX_03')
group by err_index_name, err_text
;

ERR_INDEX_NAME   ERR_TEXT                                              COUNT(*)
---------------- ----------------------------------------------------- ---------
TST_CTX_02       DRG-50857: oracle error in drsinopen                         99
                 ORA-22920: row containing the LOB value is not locked


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms