title: Reasons for getting (ORA-8102 "index key not found")
body: <p>So i have a table with an index called IDX_ATS_CALC_END_TIME. The column is a timestamp value. This column also has a trigger that automatically populates the column when another column (Interval_duration) is populated or updated.</p>
  
  <p>The trigger is below:</p>
  
  <pre><code>TRIGGER "DATAMART"."TRG_ATS_CALC_END_TIME" 
      BEFORE INSERT OR UPDATE OF INTERVAL_DURATION ON DATAMART.AGG_TIME_SUMMARY
      FOR EACH ROW
      DECLARE
  BEGIN
  IF :New.INTERVAL_DURATION &gt; 0 THEN
     :New.calc_end_time := :New.start_date_time  + pb_util.secondtointerval(:New.INTERVAL_DURATION);
  ELSE
  :NEW.CALC_END_TIME := :New.start_date_time;
  END IF;
  
      EXCEPTION
      WHEN OTHERS THEN
        pb_util.logdata(1, 'TRG_ATS_CALC_END_TIME', 'Exception Thrown in interval:  ' || :New.Interval_DURATION, SQLERRM  || ' stack: ' || dbms_utility.format_error_backtrace);
  
  END TRG_ATS_CALC_END_TIME;
  </code></pre>
  
  <p>When my table is initially populated there are no problems. My problem is that when i go to perform an insert/update on the table and try to modify this column by either directly changing the column or just updating the interval_duration column i have this error thrown: </p>
  
  <blockquote>
    <p>ORA-08102: index key not found, obj# 97523, file 4, block 244 (2)</p>
  </blockquote>
  
  <p>The index mentioned is a functioned based index. The function being used on the index is a sys_extract_utc on the calc_end_time column.</p>
  
  <p>I've spent several days trying to solve this issue. I've rebuilt the index, i have tried deleting and recreating the index. These two seem to be the common answer for this problem, but they did not work for me.
  I've analyzed the index using the following:</p>
  
  <p><code>ANALYZE INDEX IDX_ATS_CALC_END_TIME VALIDATE STRUCTURE;</code></p>
  
  <p>and it came back with no problems.</p>
  
  <p>The only time i have been able to successfully update this column without getting this error was by disabling the trigger, performing the update, and then enabling the trigger once again. This is not a viable solution for me. </p>
  
  <p>So i would like to know if anyone has ever encountered this type of problem and what other steps i can try to fix this error.</p>
  
  <p><strong>UPDATE:</strong>
  below you will find the function pb_util.secondtointerval() code:</p>
  
  <pre><code>FUNCTION SecondToInterval
    (Seconds_IN NUMBER
    )
  RETURN CONST.PBInterval
  IS
    sec            NUMBER(20, 9);
    days           NUMBER;
    hours          NUMBER;
    minutes        NUMBER;
    seconds        NUMBER(20, 9);
    IntervalAsText NVARCHAR2(32);
    ReturnInterval INTERVAL DAY(9) TO SECOND(9);
  begin
    sec     := NVL(Seconds_IN, 0);
  
    days    := trunc(sec/(24*60*60));
    sec     := sec - days*24*60*60;
  
    hours   := trunc(sec/(60*60));
    sec     := sec - hours*60*60;
  
    minutes := trunc(sec/60);
    sec     := sec - minutes*60;
  
    seconds := trunc(sec);
  
    sec     := sec - seconds;
    sec     := trunc(1000000000*sec);
  
    IntervalAsText := cast(days as nvarchar2)
      || ' ' || cast(hours as nvarchar2)
      || ':' || substr('00' || cast(minutes as nvarchar2), -2, 2)
      || ':' || substr('00' || cast(seconds as nvarchar2), -2, 2)
      || '.' || substr('000000000' || cast(sec as nvarchar2), -9, 9);
  
    --dbms_output.put_line(intervalastext);
  
    ReturnInterval := TO_DSInterval(IntervalAsText);
    --ReturnInterval := TO_DSInterval('999999999 23:59:59.999999999');
    --dbms_output.put_line(ReturnInterval);
  
    RETURN ReturnInterval;
  EXCEPTION
      WHEN OTHERS THEN
     pb_util.logdata(1, 'PB_UTIL.SecondToInterval', 'ERROR(99A): ', intervalastext);
                  dbms_output.put_line(intervalastext);
                  RAISE;
  
  end SecondToInterval;
  </code></pre>
  
  <p>this was written by my predecessor but basically all it does is turn the given numeric value and converts it into an interval value.</p>
  
  <p>Any help or suggestions are greatly appreciated.</p>
  
  <p>Thank you.</p>