use strict;
use DBI;

my $db = DBI->connect( $dbasename, $user, $pword ) or die DBI->errstr;
my $ss = $db->prepare( 'update employee set remarks = ? where emp_no=?
+ and emp_join_year = 1001 ' ) or die $db->errstr;

while( <DATA> ) {
  chomp;
  my( $remark, $emp_no, $join_year ) = split "~~";
  $ss->execute( $remark, $emp_no, $join_year );
}
$ss->finish;
$db->disconnect;
__DATA__
Test now for Ryan~~295~~200102
Test later for grinder~~29008~~200005
[download]

DBI runs just fine on both Win32 and Unix systems, thus your code will be much more portable (maybe not a big concern) and your skill set will also be more portable. This is of a greater concern, because it means you can ask for more money at your next job!

NB: rough code sketch, no guarentees that it works as advertised.

g r i n d e r

Oracle has no issues with multiple statements per session, so there is something else going on. Opening and closing the connection each time is seriously wasteful. I would first off suggest that you:

• display the $query statements via STDOUT to verify they are correctly formed
• throw an error message on an unsuccesful execution
• I don't see any "my"'s in there, so I assuming you are not using "use strict", you want to do that.

I also love the suggestion to use the native DBI Oracle drivers rather than ODBC. you may want to do one commit (depnding on your oracle defaults) after the loop after determining all has gone well.

good luck

DBI has excellent function ->trace(2), printing a string for each DBI action made. Excellent for debug. I reccomend!

pmas
To make errors is human. But to make million errors per second, you need a computer.

update: I missed the Oracle reference (or it was added at some later point). From what I remember of Oracle version 7.x, newlines will do as statement terminators.

Thanks monks! for thy replies!

The reason I use ODBC instead of DBI/DBD is that it gives me DATABASE independence
(so that I can use the same module for updating records into other RDBMS backend with no or little tweaking around with the code!).
I know connecting, updating the database and disconnecting for time the loop is excuted is dumb,
but had to get it to work
As far the semicolon at the end of the statement is concerned -
it's NOT necessary (oracle seems to be comfortable without semicols)!
I am getting the SQL statements generated into a log file and it's perfect!
And I have put up the code for printing the error if any during the Sql statement execution

So that my code looks like this now:
#@updatequeryarray is the array containing the Update statements
#for eg.: UPDATE EMPLOYEE SET REMARKS='Test now for Ryan' WHERE EMP_NO='295' AND EMP_JOIN_YEAR='200102'
$dsn=$config->{'DSN'};
foreach my $query (@updatequeryarray) {
$db = new Win32::ODBC("$dsn");
if (!$db) {
print "Cannot connect to DSN $dsn\n";
return 0;
}
$dbgstr.="\n $query";
if ($db->Sql($query)) {
print "Database Updatation Error".$db->error;
}
$db->Sql("commit");
$db->Close();
}

if (!open(DBGRESP,">>$config->{'PATH-RESPONSES'}/SQL")) {
print "Error Appending DBG file\n";
return 0;
}
print DBGRESP "$dbgstr";
close DBGRESP;

Your goal (database independence) is right, but I am not so sure about your way to achieve it.
DBI.pm is the way to database independent data access, and was designed along ODBC guidelines - early adopter. DBI allows you to move your code from Win PC with some ODBC-compliant database to i.e. Linux and database which may or may not be ODBC-compliant, just DBI-compliant driver will be enough.

You are using two layers of interfaces: (1) from your program to local database access client, and (2) from PC to database server (which might be on the same PC, LAN, or internet).
Your approach is to use Win32::ODBC for task (1), and ODBC for task (2). But, if you decide to move your code to different machine/OS, you may or may not have available Win32::ODBC there, because Win32 in module name suggests it is windows-specific module. DBI most likely will be there, so you can use DBI calls exactly as on PC.
As an added advantage, sometimes native DBD drivers (DBD::Oracle) might implement some DBI calls more efficently than standard ODBC, or you may add some nifty time-saving feature just for ORACLE, using ORACLE's SQL extension, and implementing it for other databases in less eficient, but more standard SQL.
Also, much more programmers know DBI calls, so you may have more help (dbi-user email list etc) and excellent "Programming the Perl DBI" book written by author of DBI, Tim Bunce, with Alligator Descartes (the "Cheetah book"). It describes DBI from basics to other esoteric stuff like reading SQL cursors (sets of records) to arrays of hashes, which you can use for HTML::Template etc.

I might be wrong here with performance issue (you did not mentioned how important it is for you), so you may want to impement both, and benchmark. Or maybe somebody here can advise on performance issue - or ask in dbi-users mail list.

I myself prefer DBI and am happy with it.
Just my $0.02.

pmas
To make errors is human. But to make million errors per second, you need a computer.