I don't see anything glaringly wrong, and in fact I don't know why it's
not working for you. Maybe I'm just overlooking something, but why not
include the $orasid and $server as bind variables? Like this:
my $insert_sql = qq { insert into user_history_stats (database,
server, year, week, username, cpu, io)
values (?, ?, ?, ?, ?, ?, ?)
};
my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't
prepare insert statement: " . $target_dbh->errstr;;
while ($source_sth->fetch) {
$target_sth->bind_param(1, $orasid);
$target_sth->bind_param(2, $server);
$target_sth->bind_param(3, $year);
$target_sth->bind_param(4, $week);
$target_sth->bind_param(5, $userid);
$target_sth->bind_param(6, $cpu);
$target_sth->bind_param(7, $io);
$target_sth->execute;
}
???
HTH.
Hardy Merrill
--------------------------------------------------------------------------------
>>> "Bart Kersteter" <[EMAIL PROTECTED]> 08/17/04 11:06AM
>>>
Here's the full main loop script, I'm still relatively new to DBI, so
I
may have messed things up a bit. I have gone through the perldocs a
few
times.
my $db_list = new IO::File("< /home/oracle/utils/metrics_db_list.txt")
or die "Can't open oratab file";
while (<$db_list>) {
my @db_info = split(/:/);
my $orasid = $db_info[0];
my $server = $db_info[1];
my $source_dbh = DBI->connect( "dbi:Oracle:$orasid", '<user>',
<password>',
{RaiseError => 1, AutoCommit => 0}) || die DBI->errstr;
my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", '<user>',
'<password>',
{RaiseError => 1, AutoCommit => 0}) || die
DBI->errstr;
my $source_sql = qq{ SELECT username, to_char(sample_Date, 'YYYY')
, to_char(sample_date, 'IW') , sum(cpu) , sum(IO)
from daily_user_stats
group by username, to_CHAR(sample_date, 'IW')
, to_char(sample_Date, 'YYYY')
order by 1,2 };
my $source_sth = $source_dbh->prepare( $source_sql ) or die "Can't
prepare source statement: " . $source_dbh->errstr;
$source_sth->execute;
my ($userid, $year, $week, $cpu, $io);
$source_sth->bind_columns(\$userid, \$year, \$week, \$cpu, \$io);
my $insert_sql = qq { insert into user_history_stats (database,
server, year, week, username, cpu, io)
values ('$orasid', '$server', ?, ?, ?, ?, ?)
};
my $target_sth = $target_dbh->prepare( $insert_sql ) or die "Can't
prepare insert statement: " . $target_dbh->errstr;;
while ($source_sth->fetch) {
$target_sth->bind_param(1, $year);
$target_sth->bind_param(2, $week);
$target_sth->bind_param(3, $userid);
$target_sth->bind_param(4, $cpu);
$target_sth->bind_param(5, $io);
$target_sth->execute;
}
# Clean up and exit loop
$target_dbh->commit();
$source_dbh->disconnect();
$target_dbh->disconnect();
}
The literal 'FD1P' you referred to is the SID of one of my databases.
Thanks,
Bart
Bart Kersteter
Senior DBA - Corporate Database
Assurant
576 Bielenberg Drive
Woodbury, MN 55125
[EMAIL PROTECTED]
(651) 361-5796
**************************************************************************************
This e-mail message and all attachments transmitted with it may contain
legally privileged and/or confidential information intended solely for
the use of the addressee(s). If the reader of this message is not the
intended recipient, you are hereby notified that any reading,
dissemination, distribution, copying, forwarding or other use of this
message or its attachments is strictly prohibited. If you have received
this message in error, please notify the sender immediately and delete
this message and all copies and backups thereof.
Thank you.
**************************************************************************************
