Things to remember while writing an AppEngine or Batch Process

When writing any batch process typically two operations will be performed on the database either Insert or Update in the tables. Most of the batch processes will fail on these update & insert statement.

 Typical batch process errors are : Can update value to Null, Unique constraint error ..etc


While inserting or updating a table we should remember couple (or more) of things.

While Updating:
 1. Check the row already existed  target table by using Exist or In operator.

 2. In some case we need to write sub-query to update the value, at sometime the subquery may  not result  a value & if the target field can't store blanks, the process will result in failure. Inorder to eliminate this kind issue use NVL or NVL2 functions outside sub-query

  for eg:- Update TBLA set TBL.FIELD1 = NVL((select TBL2.FIELD2 from TBL2),' ')

While Inserting:

1. Check any null values from Source table are inserting into a non-null value field in the target, then use NVL ,NVL2, case or Decode functions. Know the use these functions & use it in proper situation.

2. Check the row already existed in target table by using Exist or In operator.

3. Check any duplicate values exist in Source table itself  while inserting into target table. This one is most people will forget doing.