1

I want to read a table in Oracle which contains database links for checking availability of each database link and return only the bad results for each database link and error message.

I want to fetch this lookup table into an array, and pass the entries of db_link to a select from dualQdb_link, test all the entries of lookup to test for success or failure. This seems very hard to achieve in perl.

Any ideas?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Paul
  • 11
  • 1

1 Answers1

2

Seems pretty straightforward, something like this:

# Or whatever the column is really named ;)
my $dblinks = $dbh->selectcol_arrayref("select dbname from db_link");

for my $dblink (@$dblinks) {
  my $success = eval {
    my ($ret) = $dbh->selectrow_array("select 1 from "
      . $dbh->quote_identifier($dblink, undef, "dual") );
    $ret;
  };

  if ($success) {
    say "$dblink is up";
  } else {
    say "$dblink is down";
  }
}
hobbs
  • 223,387
  • 19
  • 210
  • 288
  • 1
    +1 for `quote_identifier()`. For pedantry points, I think the dynamic SQL should be: `"SELECT 1 FROM " . $dbh->quote_identifer('dual', undef, $dblink)`. – pilcrow Feb 28 '10 at 02:09
  • @pilcrow: Actually, you've got that backwards. It's ` $dbh->quote_identifier( 'link', 'schema', 'table' );` so it would be `"SELECT 1 FROM " . $dbh->quote_identifer($dblink, undef, 'dual')`. – cjm Feb 28 '10 at 08:33