title: Why Sqlplus bash script NOT returning error for ORA-02291 integrity constraint when "WHENEVER SQLERROR EXIT 1" is used?
body: <p>I have written a bash script to connect to sqlplus and execute all the sql scripts in a particular folder. Below is the execute method of the script.</p>
  
  <pre><code>execute() {
  if [ ! -d "$DIR_SqlFiles" ]
  then
  echo "No sql files were found. Cannot find path ${DIR_SqlFiles}"
  return 1
  fi
  echo "`date` :Connecting To ${userName}/******@${serviceName}";
  for file in `ls ${DIR_SqlFiles}/*` ; do
  echo "`date` :Executing file $file..."
  echo "`date` :SQL OUTPUT:";
  sqlplus -s ${userName}/${password}@${host}:${port}/${serviceName} &lt;&lt;EOF
  WHENEVER OSERROR EXIT 1 ROLLBACK;
  WHENEVER SQLERROR EXIT 1 ROLLBACK
  @${file};
  commit;
  quit;
  EOF
  sql_return_code=$?
  if [ ${sql_return_code} != 0 ]
  then
  echo "`date` ${file} failed"
  echo "Error code ${sql_return_code}"
  return 1;
  fi
  done
  echo "`date` :completed running all sql scripts in the ${DIR_SqlFiles} folder."
  return 0;
  }
  </code></pre>
  
  <p>The problem is when one of the .sql files has ORA-02291: integrity constraint this script will not fail. Its returning 0 and it just prints "0 rows updated" without printing the actual error.</p>
  
  <p>In other cases its working fine. For example, in case of wrong table name (ORA-00942: table or view does not exist) the script will fail and return 1.</p>
  
  <p>Could anyone please point me to the right direction. Why for the first case errors its not failing?</p>