This discussion is archived
7 Replies Latest reply: Oct 18, 2012 7:49 AM by Anne-Marie Rosa RSS

Oracle Text Error (with ORA-06532) Logged in DBA_SCHEDULER_JOB_RUN_DETAILS

921538 Newbie
Currently Being Moderated
Greetings,

I am solving a problem that relates to Scheduler and Text Index Synchronization. The setup is drescribed as the following:

1. A table with an Oracle Text Index created.
2. A procedure to synthesize the index data.
3. A job to run CTX_DDL.SYNC_INDEX on the said text.
4. The db version is Oracle 10g (forgot to extract the actual version values)

The said job is periodically being triggered at some point in an application. So the job is being triggered for some time now and at certain run, an error was found in the DBA_SCHEDULER_JOB_RUN_DETAILS view. The following details are extracted:

ERROR# : 20000
ADDITIONAL_INFO :
ORA-20000:
ORA-20000: Oracle Text Error:
DRG-50857: oracle error in drvdml.ProcessWaiting
ORA-06532: Subscript outside of limit
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.CTX_DDL", line 539
ORA-06512: at line 1

I am wondering why the ORA-06532 error was raised. I have checked that procedures and functions related to the index synthesizer do not use VARRAY. References only suggest that VARRAY can cause the ORA-06532 error. Also, I can't find any resources relating to drvdml.ProcessWaiting error at all, so I'm focusing on the subscript error.

I'm not sure if I'm solving the problem at the right point of view (checking the cause of ORA-06532). Can anyone suggest ideas, insights or a guide to solving this error? Please also advise if there's lacking information needed.



Code and Logic:

Currently, I can only supplement the code as follows:
begin
  ctx_ddl.create_preference('DUMMY_TABLE_DS', 'USER_DATASTORE');
  ctx_ddl.set_attribute('DUMMY_TABLE_DS', 'procedure', 'PCK_INDEXER.indexDummyTable');
  ctx_ddl.set_attribute('DUMMY_TABLE_DS', 'output_type', 'CLOB');
end;
/

create index DUMMY_TABLE$TEXT_I on DUMMY_TABLE(INDEX_DT) INDEXTYPE IS CTXSYS.CONTEXT ONLINE
  parameters ('DATASTORE DUMMY_TABLE_DS LEXER DUMMY_TABLE_LEXER STOPLIST 
                    DUMMY_TABLE_STOPLIST WORDLIST DUMMY_TABLE_WORDLIST SECTION GROUP PATH');

begin
  dbms_scheduler.create_job(
    job_name => 'MY_USER.SYNC_DUMMY_TABLE',
    job_type => 'PLSQL_BLOCK',
    job_action => 'begin ' ||
                  '  ctx_ddl.sync_index(''DUMMY_TABLE$TEXT_I''); ' ||
                  'end;',
    start_date => systimestamp at time zone '+8:00',
    comments => 'Synchronizes the oracle text index of the table DUMMY_TABLE.',
    auto_drop => FALSE,
    enabled => TRUE);
end;
/

create or replace package body PCK_INDEXER
as
  --...
  procedure indexDummyTable(paramRowId in varchar2, paramClob in out nocopy clob) is
    rec DUMMY_TABLE%ROWTYPE;
    xmlForIndex xmltype;
  begin
    select *
      into rec
      from DUMMY_TABLE
     where ROWID = paramRowId;
     
     -- At this point, the logic is to create data in XML and put it in xmlForIndex variable.
     -- Something like the one below:
     -- select xmlelement("GLOBAL", xmlelement("DUMMY_TEXT", xmlforest(DUMMY_TEXT.COL_1...
     -- The xmltype data is created by combinations of xml functions
     
     processClobToIndex(paramClob, xmlForIndex.getClobVal());
     -- The procedure processClobToIndex just copies the xml's clob value 
     -- to the paramClob variable in segments of 2000 characters.
     -- The procedure just uses a loop and combinations of substr functions to parse the xml's clob value
  end indexLinkage;

end PCK_INDEXER;
Thank you very much in advance!

Best Regards.

Edited by: arvinL1 on Mar 3, 2012 7:43 AM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points