USER_DATASTORE Procedure for Text Index fails with DRG-12604 DRG-50857 (Doc ID 730503.1)

Last updated on OCTOBER 30, 2014

Applies to:

Oracle Text - Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 20-Jun-2012***

Symptoms

After the index is built, a search of the $I table token_text reveals that not all data is being indexed. Even though the index appears to be created without errors in SQL*Plus, a query against CTX_USER_INDEX_ERRORS exposes errors from several rows.

SQL> connect erstschema/test1

exec ctx_ddl.drop_preference('test_datastore');

-- Preferences for Textindex
-- datastore
begin
ctx_ddl.create_preference('test_datastore', 'USER_DATASTORE');
ctx_ddl.set_attribute ('test_datastore', 'PROCEDURE', 'test_proc');
end;
/

SQL> connect erstschema/test1
SQL> drop index my_ind;

SQL> create index my_ind on ersschema.cust_rsum (text_dummy)
indextype is ctxsys.context
parameters
('DATASTORE test_datastore
  SECTION GROUP ctxsys.AUTO_SECTION_GROUP
');

SQL> select * from ctx_user_index_errors;

MY_IND 25-JUL-08 AAC1MaABNAAABAiAAA
DRG-12604: execution of user datastore procedure has failed
DRG-50857: oracle error in drsinopen
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.DBMS_LOB", line 833
ORA-06512: at "ERSTSCHEMA.TEST_PROC", line 9
ORA-06512: at line 1


PROCEDURE FAILS:
================

CREATE OR REPLACE PROCEDURE test_proc
(p_rowid IN ROWID,
p_clob IN OUT NOCOPY CLOB)
AS
BEGIN
FOR r1 IN (SELECT * FROM ersschema.cust_rsum WHERE ROWID = p_rowid)
LOOP
DBMS_LOB.WRITEAPPEND (p_clob, 12, '<textfield1>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.job_ttl_txt), r1.job_ttl_txt);
DBMS_LOB.WRITEAPPEND (p_clob, 13, '</textfield1>');
DBMS_LOB.WRITEAPPEND (p_clob, 12, '<textfield2>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.spcf_skl_txt), r1.spcf_skl_txt);
DBMS_LOB.WRITEAPPEND (p_clob, 13, '</textfield2>');
DBMS_LOB.WRITEAPPEND (p_clob, 12, '<textfield3>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r1.awrd_cert_txt), r1.awrd_cert_txt);
DBMS_LOB.WRITEAPPEND (p_clob, 13, '</textfield3>');
FOR r2 IN
(SELECT * FROM ERSSCHEMA.RSUM_WORK_HIST WHERE rsum_work_hist.cust_rsum_sk = r1.cust_rsum_sk)
LOOP
DBMS_LOB.WRITEAPPEND (p_clob, 14, '<detailfield1>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r2.resp_txt), r2.resp_txt);
DBMS_LOB.WRITEAPPEND (p_clob, 15, '</detailfield1>');
END LOOP;
FOR r3 IN
(SELECT * FROM ERSSCHEMA.RSUM_EDUC_HIST WHERE rsum_educ_hist.cust_rsum_sk = r1.cust_rsum_sk)
LOOP
DBMS_LOB.WRITEAPPEND (p_clob, 14, '<detailfield2>');
DBMS_LOB.WRITEAPPEND (p_clob, LENGTH (r3.crse_stdy_txt), r3.crse_stdy_txt);
DBMS_LOB.WRITEAPPEND (p_clob, 15, '</detailfield2>');
END LOOP;
END LOOP;
END;
/



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