Thanks to all for your suggestions... I've finally gotten things working. The following is a working example. The one issue I have left to handle is to add some error chceking and parameterize a few things to make the script more transportable. One little glitch is that the final fetch for the source handler appears to be an empty set, and I'm getting the following error even with the check in place to see if userid is empty:
Use of uninitialized value in string eq at ./collect_user_metrics.pl line 62, <GEN0> line 1. Thanks again for your help and suggestions. Bart *---------------------- use strict; use DBI; use IO; use POSIX qw(uname); my $db_list = new IO::File("< /home/oracle/utils/metrics_db_list.txt") or die "Can't open oratab file"; my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", 'metrics', 'metr1cs', {RaiseError => 1, AutoCommit => 1}) || die DBI->errstr; 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 (<$db_list>) { my @db_info = split(/:/); my $orasid = $db_info[0]; my $server = $db_info[1]; my $source_dbh = DBI->connect( "dbi:Oracle:$orasid", 'metrics', 'metr1cs', {RaiseError => 1, AutoCommit => 0}) || die DBI->errstr; my $target_dbh = DBI->connect( "dbi:Oracle:fd1p", 'metrics', 'metr1cs', {RaiseError => 1, AutoCommit => 0}) || die DBI->errstr; # set up bind variables 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 ); while ($source_sth->fetch) { if ( not $userid eq "" ) { $target_sth->execute($orasid, $server, $year, $week, $userid, $cpu, $io); } } # Clean up and exit loop $target_dbh->commit(); $source_dbh->disconnect(); } $target_dbh->disconnect(); 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. **************************************************************************************