RE: Trouble with bind variables

[Bart Kersteter]

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.
**************************************************************************************