7 Replies Latest reply: Oct 18, 2012 9:49 AM by Anne-Marie Rosa RSS

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

    921538
      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