use warnings;    
use strict;
use Text::CSV;
use DBD::Oracle;

my $exitStatus = 0;

# Connect to database and disable autocommit
my $dbAgent = OracleAgent->dbLogin(dbName);
   $dbAgent->connect();
   $dbAgent-> setAutoCommit(0);

#@csvFile holds all csv files list to load in db
foreach my $file (@csvFile) 
 {
   chomp ($item);
   &insertRecords($file);
 }

$logFile-> ("Exit status");

#----------------

sub insertRecords 
{
my $csv;
my $fileToInsert = shift;
my $row;
my $SQL;
my $sth;
my $dbh;
my $rc;

open my $fh, "<", $fileToInsert or die "$filetoInsert: $!"

$logFile -> ("Working under $file")

while ($row = $csv->getline ($fh))
     {
     $SQL1 = "Insert into TAB1 (sample_date, server, first, n1, n2)
             values (?,?,?,?,?)";
     $sth = prepare($SQL1)
     $sth -> execute($row[0], $row[1], $row[2], $row[3], $row[4])
              || {
                   logFile($DBI::errstr);
                   $dbAgent->rollback;
                   $rc=0;

                    #if error in insert then update status table
                      $SQL3 = "Insert into STATUS (sample_date, server, cvs_file, row_count, status)
                                values (sysdate,$row[1],$file,$rc,'FAILURE')";

                      $sth = executeQuery($SQL3);
                   }
             else
                 {

                    #if no error in insert then update status table and commit everything
                     $SQL2 = "Insert into STATUS (sample_date, server, cvs_file, row_count, status)
                     values (sysdate,$row[1],$file,$rc,'SUCCESS')";
                     $sth = executeQuery($SQL2);
     $rc++;
      }

 close $fh;

 $dbAgent->commit;