in reply to Perl oracle insert error handling

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?

Replies are listed 'Best First'.
Re^2: Perl oracle insert error handling
by homer4all (Acolyte) on Dec 23, 2014 at 17:28 UTC
    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]
[reply]
[d/l]

      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
[reply]
[d/l]
        poj... thanks a TONE for this update.. it has really teach me many things and I'm currently testing my code with your suggestions. I can use most of the logic from it and need to do change as per requirements.

        EXCELLENT and I'm waiting to be expert in perl to give back to community

[reply]
      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.

[reply]

In Section Seekers of Perl Wisdom