Since you've got several syntax errors that prevent your code from even compiling, I'm guessing you're fairly new to Perl. In that case, I think you've put too much on your plate to start with. You can fix a lot of the issues with the code yourself; take a step back and approach the problem step by step. For example, Step 1: connect to the database. Put this in a script and attempt to run it:

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);
[download]

Try to run it: Does it work, or does it print an error? If it prints an error, try to fix it, or post it here if you can't figure it out on your own. Then, Step 2, add the following to the file:

foreach my $file (@csvFile) 
 {
   chomp ($item);
   &insertRecords($file);
 }

sub insertRecords 
{
 my $fileToInsert = shift;
}
[download]

Again, try to run it, and fix the problems as they show up. And so on.

Also, remember the Basic debugging checklist, and if you need help fixing individual problems, remember How do I post a question effectively?

Thanks for the suggestion and I 100% agree to it.

My database connection, inserting into database, committing record everything works

I will go with one problem at a time.

My very first and most important problem is to have WHILE IF ($sth->err) loop working..I have changed my initial code little bit to move further.

somehow code doesn't like 2 close $fh; in the code and still logfile says 1 row commited where zero rows inserted actually.

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]);
        if ($sth->err)
           {
             $rc=0;
             $dbAgent->rollback();
             close $fh;
           }
       $rc++;
  }
 close $fh;
 $dbAgent->commit;
[download]

You only need to prepare the statements once

#!perl
use strict;
use Text::CSV;
use DBD::Oracle;

my $csv = Text::CSV->new ( { binary => 1 } )  
          or die "Cannot use CSV: ".Text::CSV->error_diag ();
my $dbh = dbh(); # connect

my $SQL1 = "INSERT INTO TAB1 (sample_date, server, first, n1, n2) 
            VALUES (?,?,?,?,?)";
my $sth1 = $dbh->prepare($SQL1);

my $SQL2 = "INSERT INTO STATUS (sample_date,server,csv_file,row_count,
+status)
            VALUES (sysdate,?,?,?,?)";
my $sth2 = $dbh->prepare($SQL2);

my @csvFile = ('1.csv','2.csv','3.csv');
for my $file (@csvFile){
  insertRecords($file);  
}
$dbh->disconnect;

sub insertRecords {
  my $file = shift;
  print "Working under $file\n";
  open my $fh,'<',$file or die "$file : $!";
  
  my $status = "SUCCESS";
  my $server;
  my $rc = 0;
  while (my $row = $csv->getline ($fh)){
    ++$rc;
    $server = $row->[1];
    print join ",",@$row[0..4],"\n";
    if ( $sth1->execute(@$row[0..4]) ){
      # ok
    } else {
      $status = "FAILURE";
      last;
    }
  };
  
  if ($status eq 'FAILURE'){
    $dbh->rollback;   
    $rc = 0;
    print "ERROR - Rolled back $file\n";
  }
  
  $sth2->execute($server,$file,$rc,$status);
  $dbh->commit;
  close $fh;
}

# connect
sub dbh {
  my $host = "localhost";
  my $sid  = 'xe';
  my $user = '';
  my $pwd  = '';
  my $dsn = "dbi:Oracle:host=$host;sid=$sid";
  my $dbh = DBI->connect($dsn, $user, $pwd, 
            { AutoCommit => 0,
            })  or die "$!";
  return $dbh
}
[download]

poj

My database connection, inserting into database, committing record everything works

Really? Then next time show us that code, since the code in the OP certainly doesn't work.

Side note: The first query ($SQL1) is prepared and executed, avoiding dynamic SQL and its ills. Bravo! But the second query ($SQL3) uses dynamic SQL. Perhaps you shopuld prepare that one as well.

Yes, you are right. There will be some query/syntax not written in best method since I'm still learning this.

Any recommendations?

Just prepare it the same way you did the first one, that is, by using placeholders where any data will be provided by variables. Then pass the variables in the execute statement. This will define the variables as parameters and guarantee they will not be used as DDL, DML, or TCL, even if they include malicious values.

It looks like your problems are not so much with the logic but with syntax instead. If you know some Perl, you should probably start with cleaning that up to make your code runnable. Otherwise, start with perlintro and perlsyn.

Crossposted to StackOverflow. It's considered polite to inform about crossposting. You've been told this before.

Not intentionally left out that info but honestly didn't remember to put that. Since trying to sort out this issue for sometime now is really stressing me out.