Home: Perl Programming Help: DBI:
Problem with loading data into table from file

New User

Sep 8, 2010, 7:52 AM

Views: 5391
Problem with loading data into table from file

Hi Everyone,

I'm new to Perl scripting .I'm facing small problem while loading data from file into table.
i need to create Perl script for loading data from file with "|" separated into oracle table.
At the end of process,
i need to display statistics like
Total Number of records in file :- 10
total successfull records : 6
Total failed records : 4

Now i'm not able to print statistics at the end of process.
for your reference i have attached script i have written for this.
Please help me on this,i need this as soon as possible.

Thanks in advance.
Attachments: loadfilelinebyline.pl (1.30 KB)


Sep 8, 2010, 5:06 PM

Views: 5380
Re: [vinayaseshu] Problem with loading data into table from file

Hi, Vinayaseshu,

My comments in bold.

use DBI;
use strict;
use warnings;

#extablishing connection to database
my $dbname = "oracle11gr2";
my $dbuser = "scott";
my $dbpass = "tiger";

# AutoCommit => 1 obviates the commit statements in your code.
# RaiseError => 1 ensures DBI will 'die' if a statement fails (so you can test for $@ after eval {}).

my $dbh = DBI->connect("dbi:Oracle:$dbname", $dbuser, $dbpass, { AutoCommit => 1, RaiseError => 1 } )
|| die "Unable to connect to $dbname: $DBI::errstr\n";

#truncating table for fresh loading

# No need to prepare single-use statments.
$dbh->do("truncate table emp_test");
if ( $@ )
die "DBI threw an exception attempting to truncate emp_test!: $@";

#declaring required variables.
my $no="";
my $abc="";
my $ins ="";
my $inssth="";
my $totalrecords=0;
my $success=0;
my $fail=0;

#looping through all rows of data and insert into table
my $data_file="./examples/names.txt";
open(NAMES, $data_file) || die("Could not open file!");

# There's no benefit to using prepared statements unless you
# re-use them.
$inssth = $dbh->prepare("insert into emp_test values(?,?)");

foreach my $line (<NAMES>)
$totalrecords +=1;
$inssth->execute($no, $abc);
# Here are your missing stats.
if ( $@ )
# Error
print STDERR "DBI threw an exception inserting values ($no, $abc): $@\n";
} else {
# Success

# Tidy is as tidy does.

print "total Number of records in file is: " . $totalrecords . "\n";
print "total Number of successfull loaded records is: " . $success . "\n";
print "total Number of failed records from file is: " . $fail , "\n";

#commiting at end of program