I have never seen a database application where data is stored and is never fetched. In simple words fetching of data is the most important and essential part of any data driven application. Today I will discuss some common mistakes that are done while fetching data from databases

Fetch across Commit on IMPLICIT Cursor:

Most developers/database administrators follow this common practice where data is fetched across commit points.

begin
  for x in ( select * from MYTABLE where (COL3=‘true') )
  loop
   /* some application logic will be here */
   update MYTABLE set COL2=‘myvalues' where MYPKCOL = MYTABLE.pk;
   if (100 rows are iterated)
     then
     commit;
   end if;
  end loop;
end;

Losing Transaction Integrity:

The problem with this approach is that it eliminates the concept of transaction. If your records that are to be updated are part of the same transaction and say after 4 commit points you run into some issue then you don't have the way to undo the update you have already done. It is not possible to restart such a transaction.

ORA-01555 Error:

Secondly you can easily get ORA-01555 (ORA-01555: Snapshot too old) error. This is because the UNDO needed for a query does not exist any longer. By issuing commit we tell Oracle that UNDO can be reused. However if we read and modify the same table then it increases the possibility of getting ORA-01555 error. 

An implicit cursor is issued by PL/SQL whenever a SQL statement is executed directly in your code. In the implicit cursor above, our long running query after issuing the commit may require the UNDO that is just released for reuse. Remember you will not get ORA-1555 for update rather you will get it for reads. If above code snippet reads our table MYTABLE using an index operation and blocks are read in the order say block B5, B6, B7, B10, B25, B6, B7 and so on, then our code can in effect come back to the block. However every time it comes back, it will need the version of block that existed when query had begun. As a matter of fact the UNDO generated from our first commit iteration is lost because we made Oracle reuse it and hence our code will not be able to get that version anymore.

Fetch across Commit on FOR UPDATE Cursor:

If you have a FOR UPDATE cursor and a fetch across commit is attempted on this cursor then it would result in ORA-01002 (ORA-01002: Fetch out of Sequence) error. 

This is because the locks are released and cursor is gone when the commit is done. There is nothing to fetch from and hence you get this error.  

begin
  for x in ( select * from MYTABLE where (COL3=‘true') for update)
  loop
   /* some appl. logic over here */
   update MYTABLE set COL2=‘myvalues' where MYPKCOL = MYTABLE.pk;
   commit;
  end loop;
end;
/

begin
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 2

ORA-01002: Fetch out of Sequence error is caused because we tried fetch across commit on FOR UPDATE cursor and ORA-06512: at line 2 indicates that line number 2 in the PL/SQL code caused above error.

Ensuring Transaction Integrity:

In order to prevent such problems you must ensure that your data is not fetched across commits and the transaction integrity and commit are preserved only at the transactional boundaries. Read Again!!

 More Database Articles
   Database Security: Step by step guideline
   Efficient Oracle Migration, Amazing Tricks!
   Add Flexibility to your database – Use Database Abstraction Layer
   DBA Tips for Speeding Up Large Table Updates!!
   Oracle Security Guide: Beware of all DBAs having full OS access!!