list,
I wrote a simple test script to update Oracle database, but it hung on the
line of $dbh->do($sql2).
Oracle server: NT, oracle version 8.0.3
my script running on W2000, with perl 5.6.1.633, DBD-ORACLE and DBI 1.34
The select statement "$sql1" works fine, but the script hung on the line of
$dbh->do($sql2) while trying to update oracle database.
(Note: The $sql2 statement worked fine from sqlplus 8.1.7 on my PC. The
records were updated succesfully.)
Thanks in advance.
Chang
The following is my trace for the line of $dbh->do($sql2) and my script:
TRACE:
-> do in DBD::_::db for DBD::Oracle::db (DBI::db=HASH(0x1d6a7b0)~0x1d69cd8
'UPDATE tec_t_evt_rep SET status = 20
WHERE (date_reception < 1055170556 and date_reception > 1055170556
- 950400 )') thr#01ABF200
1 -> prepare for DBD::Oracle::db (DBI::db=HASH(0x1d69cd8)~INNER 'UPDATE
tec_t_evt_rep SET status = 20
WHERE (date_reception < 1055170556 and date_reception > 1055170556
- 950400 )' undef) thr#01ABF200
New DBI::st (for DBD::Oracle::st, parent=DBI::db=HASH(0x1d69cd8), id=)
dbih_setup_handle(DBI::st=HASH(0x1ab5070)=>DBI::st=HASH(0x1d69c30),
DBD::Oracle::st, 1d6aac8, Null!)
dbih_make_com(DBI::db=HASH(0x1d69cd8), 025D9C54, DBD::Oracle::st, 240,
00000000) thr#01ABF200
dbd_st_prepare'd sql f5
dbd_describe skipped for non-select (sql f5, lb 80, csr 0x25d784c)
1 <- prepare= DBI::st=HASH(0x1ab5070) at DBI.pm line 1345 via test_ora.pl
line 50
-> execute for DBD::Oracle::st (DBI::st=HASH(0x1ab5070)~0x1d69c30)
thr#01ABF200
Script:
#!c:\perl5.6.1.633/bin/perl
use strict;
use DBI (); # database independent interface for Perl
sub main()
{
my $duration = 864000; # (1 week = 60 x 60 x 24 x 8)
my $duration1 = $duration + 86400;
my $timetoclear;
my($UTCTime)= time();
sprintf("%d\n", $UTCTime);
print "current time: $UTCTime\n";
print "duration: $duration\n";
$timetoclear= $UTCTime - $duration;
print "timetoclear: $timetoclear\n";
my $sql1="select date_reception,
hostname,
status,
msg
from tec_t_evt_rep
where (date_reception < $timetoclear and date_reception >
$timetoclear - $duration1 );
#
my $sql2="UPDATE tec_t_evt_rep SET status = 20
WHERE (date_reception < $timetoclear and date_reception >
$timetoclear - $duration1 )";
print "connecting to Oracle... ";
my $dbh = connect_db();
print "done\n";
#
print "preparing select statement...\n";
my $sth = $dbh->prepare($sql1) or die "Failed to prepare: $DBI::errstr";
print "done\n";
#
print "executing prepared select statement...\n";
my $rv = $sth->execute();
print "returned value: $rv\n";
print "done\n";
while (my ($drep, $hname, $sts, $msg) = $sth->fetchrow_array)
{
printf ("%12s %15s %3s %30s\n", $drep, $hname, $sts, $msg);
}
#
print "performing update statement.....\n";
my $rows=$dbh->do($sql2) or die "Failed to Update: $DBI::errstr";
print "number of rows: $rows\n";
print "done\n";
#
print "indicate no more data... ";
$sth->finish();
print "done\n";
#
print "disconnecting from Oracle... ";
$dbh->disconnect or warn $dbh->errstr;
print "done\n";
}
sub connect_db()
{
my $dbh1;
my $db = 'ORADB'; #should it be oracle?
my $hostname = 'host=orahost';
my $sid = "sid=orasid";
my $user = 'orauser'; # fake username... use your own
my $password = 'orapasswd'; # fake password... use your own
my $dsn = "dbi:Oracle:$db;$hostname;$sid;$user;$password";
# Here we make a reference to the database handle object (hash).
my $tracefile = 'trace';
my $trace_level = 4;
my $dbit = DBI->trace($trace_level, $tracefile);
$dbh1 = DBI->connect( $dsn, $user, $password, {
PrintError => 1,
RaiseError => 1,
AutoCommit => 1
}
) || die "Can't execute statement: $DBI::errstr";
return( $dbh1 );
}
main();
exit;
**************************************************************
This message, including any attachments, contains confidential information intended
for a specific individual and purpose, and is protected by law. If you are not the
intended recipient, please contact sender immediately by reply e-mail and destroy all
copies. You are hereby notified that any disclosure, copying, or distribution of this
message, or the taking of any action based on it, is strictly prohibited.
TIAA-CREF
**************************************************************
