Fetch-out-of-seq in O*6, PL/SQL 1.0.39

From: Edith Sandy <esandy_at_mitre.org>
Date: 27 Mar 1995 20:23:38 GMT
Message-ID: <esandy-270395150358_at_esandy.mitre.org>


I'm getting a sporadic (and as far as I can tell, unrepeatable) fetch-out-of-sequence in a PL/SQL program whose structure outline follows. Can anyone suggest where to look for the cause? Environmental conditions? Phases of the moon? (We're stuck at this release level -- Oracle 6, PL/SQL 1.0.39,SQL*PLUS 3.0.13 -- for the duration!)

The idea of this program is to go through the records in one table, and modify other tables based on its contents; to record each successfully processed transaction in log_table, and to record any data anomalies (found by the unsuccessful SQL statements) in err_table.

Most of the time all this seems to work -- it goes through the loop, recording errors or successful transactions as appropriate. However, once in a while, after recording (correctly) an error in log_errors, it gets a fetch_out_of sequence and bombs out on the outside exception handler. WHY??? (I THINK when it fails it does so after a select rather than update, but can't swear to this. I also THINK when it happens at all, it's on the first record in the cursor. I also THINK it is not repeatable -- that is, it works the next time round with the same data!)

Note that logmsg is changed before EVERY SQL statement. Note also that there are no statements between the end of the block inside the FOR loop and the end of the FOR loop. Lastly, it only READS the table the cursor is on, it doesn't WRITE it!

Any light you can help shed on what's going on -- (and how to fix it!) will be much appreciated!

DECLARE
  CURSOR ptr IS SELECT FROM some_table WHERE some_condition;
...
...

  BEGIN
  do_some_boilerplate;
  FOR rec IN ptr LOOP
    BEGIN

      logmsg := 'first select ' || rec.name ;
      SELECT .. INTO ...;
      logmsg := 'second select' || rec.name ;
      SELECT .. INTO ...;
      logmsg := 'first update ' || rec.name ;
      UPDATE some_other_table
        SET some_field=some_value WHERE some_condition;
      IF SQL%ROWCOUNT = 0 THEN RAISE NO_DATA_FOUND;
      ELSIF SQL%ROWCOUNT > 1 THEN RAISE TOO_MANY_ROWS;
      END IF;
      logmsg := 'first insert ' || rec.name ;
      INSERT INTO some_other_table
        VALUES (some_value_list);
      INSERT INTO log_table (name,action)
        VALUES (rec.name,'Processed successfully');
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        ROLLBACK;
        errcode := sqlcode;
        errmsg  := substr(sqlerrm,1,80);
        INSERT INTO err_table(sqlcode, sqlerrm,'In Loop: ' || comments)
          VALUES(errcode, errmsg,logmsg);
        COMMIT;

    END;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
        ROLLBACK;
        errcode := sqlcode;
        errmsg  := substr(sqlerrm,1,80);
        INSERT INTO err_table(sqlcode, sqlerrm,comments)
          VALUES(errcode, errmsg,logmsg);
        COMMIT;
        RAISE PROGRAM_ERROR;

END; Received on Mon Mar 27 1995 - 22:23:38 CEST

Original text of this message