kshsqlplus

Data commits to database without explicit commit


I have shell script which inserts/updates data into oracle tables, what I don't understand is there is no explicit COMMIT in the script but data is committed to database, how is this possible?

I have checked that for sqlplus autocommit is off (by connecting to sqlplus and run SHOW AUTOCOMMIT;)

#! /bin/ksh

function Exec_Sql 
  { 
    echo === Executing sql:
    echo === $sql_text
    print "$sql_text;" | sqlplus -s / 2>&1 | grep -iE 'ora|error|sp2'
    if [ ! $? -eq 0 ] #No error found
    then
      v_sqlerrm=""
      return 0 #No Errors
    else
      v_sqlerrm="Contact Support - Check Log File" 
      return 1 #Error Found 
    fi
  }


sql_text="UPDATE asr_test_tab SET f_ind = 'Y'"
if Exec_Sql; then
  echo "=== Update SQL OK "
else
  echo "=== Update SQL Failed "
fi

echo Complete: `date`
exit 0;

Solution

  • In oracle along with AUTOCOMMIT, there is an EXITCOMMIT which is enabled and making all the statements COMMIT by default when the current session ends.

    Check this on login to sqlplus.

    SHOW EXITCOMMIT;