Using Perl to select 1 from@db_link where db_link comes from each entries of a lookup table

Question

I want to read a table in oracle which contains database links for checking availabilty 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?

Answer 1

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";
  }
}

Answer 2

You have given me very useful advise and encourgages me to use perl further.

Lastly, how do i get all of the lines of "$dblink is down" into one $l_msg string. The $l_msg string would ideally contain the oracle error: i.e. $dblink is down with error $l_oraerror" and so on.

The hardest bit must be getting the error returned by oracle on failure. I cannot see a way to solve this.