You're doing better than you think. There are a few errors that interfere with what you want.

In query_databases(), %dbs is declared my inside the loop over databases. The ORACLE2 pass will destroy the ORACLE1 data. Solution: move my %dbs; outside the for (@DB) {} loop, and plan on an extra top level of keys in the hash labeling which db the data is from.
Your sub does not return a useful value, so all the lexical data you've collected is being lost on exit. Return a reference to the %dbs hash by adding the last line to the sub: return \%dbs;
When you call query_databases(), you should assign the result to a variable. That can then be passed to the do_compare() sub.

I'm not clear on exactly what you want to compare, but all the data collected in the queries is available in the return from query_databases(). Your sub will start out:

sub do_compare {
    my $dbs = shift;
    my $the_results;
#   compare stuff and set $the_results
    return $the_results;
}
[download]

I haven't examined your database queries, assuming they do what you want.

After Compline,
Zaxo

Comment on Re: References and passing data to a different sub. Select or Download Code

Replies are listed 'Best First'.
Re: Re: References and passing data to a different sub. by bcole23 (Scribe) on Oct 10, 2002 at 22:00 UTC
Thank you both. I'm glad that it's not too bad. What this does is creates a hash of tables for a db schema with the number of rows. `DB1 Table1 = 4500 rows Table2 = 500 rows DB2 Table1 = 4502 rows Table2 = 500 rows Table3 = 1000 rows` [download] and then compare the two result sets to detirmine extraneous tables or difference in rowcounts. I will post an update of my code after a little work. UPDATE #!/usr/local/bin/perl -w use strict; use DBI; use Env; my @DB = ('DB1','DB2'); my $user = "user"; my $passwd = "passwd"; my $schema = "USER"; my $DBHome = "/U01/oracle/ora817"; $ENV{"ORACLE_HOME"} = $DBHome; my %dbs; &query_databases(); my $result = do_compare(); sub query_databases { for (@DB) { my ($dbh,$sth,@TABLE_NAMES,$TABLE_NAME,$rowcount,%result); my $db = $_; # Login to the database $dbh = DBI->connect("dbi:Oracle:$db",$user,$passwd,{AutoCommit => +0, RaiseError => 1}) \|\| die("Oracle login FAILED, $DBI::errstr"); $sth = $dbh->prepare("select TABLE_NAME from dba_tables where owne +r=?"); $sth->execute( $schema ) \|\| die $dbh->errstr; while ( $TABLE_NAME = $sth->fetchrow_array ) { push(@TABLE_NAMES,$TABLE_NAME); } die $sth->errstr if $sth->err; for $TABLE_NAME ( @TABLE_NAMES ) { $sth = $dbh->prepare("select count(*) from $schema.$TABLE_NAME" +); $sth->execute \|\| die $dbh->errstr; while ($rowcount = $sth->fetchrow_array ) { $dbs{$db}->{$TABLE_NAME} = $rowcount; } } $dbh->disconnect; } } sub do_compare { for my $key ( keys %dbs ) { print "Database is $key\n"; for my $key2 ( sort keys %{$dbs{$key}} ) { print "$key2\t $dbs{$key}->{$key2}\n"; } } } [download] Well, I'm no expert and had some troubles referencing the %dbs hash, so I made it "global" and this will print out everything. However, now I need to compare the two hashes and report tables that only exist in one hash, then compare rows if they exist in both. It's late in the day and I'm coming up blank on where to even start. :(	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: Re: References and passing data to a different sub.
by bcole23 (Scribe) on Oct 10, 2002 at 22:00 UTC

DB1
   Table1 = 4500 rows
   Table2 = 500 rows
DB2
   Table1 = 4502 rows
   Table2 = 500 rows
   Table3 = 1000 rows
[download]

UPDATE

#!/usr/local/bin/perl -w

use strict;
use DBI;
use Env;

my @DB = ('DB1','DB2');
my $user = "user";
my $passwd = "passwd";
my $schema = "USER";
my $DBHome = "/U01/oracle/ora817";
$ENV{"ORACLE_HOME"} = $DBHome;
my %dbs;

&query_databases();
my $result = do_compare();

sub query_databases {
  for (@DB)  {
    my ($dbh,$sth,@TABLE_NAMES,$TABLE_NAME,$rowcount,%result);
    my $db = $_;

# Login to the database
    $dbh = DBI->connect("dbi:Oracle:$db",$user,$passwd,{AutoCommit => 
+0, RaiseError
=> 1}) || die("Oracle login FAILED, $DBI::errstr");

    $sth = $dbh->prepare("select TABLE_NAME from dba_tables where owne
+r=?");
    $sth->execute( $schema ) || die $dbh->errstr;

    while ( $TABLE_NAME = $sth->fetchrow_array ) {
      push(@TABLE_NAMES,$TABLE_NAME);
    }
    die $sth->errstr if $sth->err;
    for $TABLE_NAME ( @TABLE_NAMES ) {
       $sth = $dbh->prepare("select count(*) from $schema.$TABLE_NAME"
+);
       $sth->execute || die $dbh->errstr;

       while ($rowcount = $sth->fetchrow_array ) {
          $dbs{$db}->{$TABLE_NAME} = $rowcount;
       }
    }
    $dbh->disconnect;
  }
}

sub do_compare {
  for my $key ( keys %dbs ) {
       print "Database is $key\n";
       for my $key2 ( sort keys %{$dbs{$key}} ) {
          print "$key2\t $dbs{$key}->{$key2}\n";
       }
   }
}
[download]

[reply]
[d/l]
[select]