Avoid "Fetch across Commit"

David Clement

September 2004

Oracle versions 7-8

Oracle programming language engineers have named one particular coding technique the "fetch across commit." The logic is as follows, using Pro*C/C++ syntax for the example.

          exec sql declare cc cursor for select * from t;
          exec sql open cc;
          for (; sqlca.sqlcode == 0;) {
            exec sql fetch cc into :cc_rec;
            // process the fetched record in some way
            // and save the work
            exec sql commit;
          }
         exec sql close cc;

The characteristic feature of this programming technique is to fetch from a cursor, commit, and fetch again from the same cursor. This increases the risk of encountering the ORA-01555 error, because each fetch has to check for the original snapshot to ensure read consistency at the same time as each commit moves the rollback segment tail and makes that same snapshot more likely to be overwritten by the rollback segment head.

The simplest way to avoid the "fetch across commit" construction is to postpone the commit statement until after the close statement, as follows.

          exec sql declare cc cursor for select * from t;
          exec sql open cc;
          for (; sqlca.sqlcode == 0;) {
            exec sql fetch cc into :cc_rec;
            // process the fetched record in some way
          }
          exec sql close cc;
          // Save the work
          exec sql commit;