Fetch all records using Perl DBI

dbi, perl

Solution

It also depends on how you are structuring your overall script. Your `get_data()` call only allows a single pair of values to be returned. I see at least a couple options: either return a hash (reference) containing all the data and let the `main` assemble it, or use the loop constructs mentioned previously and fabricate the message body inside the subroutine, returning only a single scalar string.

To return all the data as a hash reference, the `get_data` subroutine might look like this (note I'm using `fetchall_hashref` instead of `fetchrow_hashref`:

sub get_data
{
  undef $/;
  open (my $QFH, "< /sql/summary.sql") or die "error can't open this file $!";
  my $sth= $dbh_oracle->prepare(<$QFH>) or
      die ("Cannot connect to the database: ".$DBI::errstr."\n");
  $sth->execute;
  close $QFH;
  my $hash_ref = $sth->fetchall_hashref('MYTABLE');
  $sth->finish;
  return $hash_ref;
}

And you call it from `main` and use the output as follows:

my $hash_ref = get_data();
my $message = "";
foreach my $table (sort keys %$hash_ref) {
    $message .= join(":", "Alert", $table, $$hash_ref{$table}{'FLAG'}) . "\n";
}

This will result in `$message` containing:

Alert:INBOUND_DATA:0
Alert:MSGS_BY_DIM:0
Alert:OUTBOUND_DATA:0

And you may want to politely:

$dbh_oracle->disconnect;

before you exit.

This has some problems, for example you've got the SQL stashed in an external script, but I have resorted to hard-coding the key (MYTABLE, which I am presuming is unique in your query) and the value (FLAG) in the script, which will be limiting later when you want to expand on this.

Problem

Original source