Re: I need outputting the results of a query in a formatted way to a scalar, so that I can email the results

[hippo]

Or do I just need to fix the syntax.

That will be needed, yes. You should move the open, close and format statements outside the loop. You should ensure that you have alternate format strings with values. Here's an SSCCE to get you started using a subset of the form:

#!/usr/bin/env perl 
use strict;
use warnings;

my $report = print_query_results ();

print "Here comes my report: \n$report\n";
exit;

sub print_query_results {
    my @foo = ([9, 8, 7], [6, 5, 4]);
    my $blurb = 'Whatever';
    my $report;
    my ($sql_id, $min_last_load_time, $buffer_gets);
    format REPORT =
@|||||||||||||||@||||||||||||||||||||@|||||||||||||||
'SQL_ID',       'min_last_load_time','buffer_gets'
@|||||||||||||||@||||||||||||||||||||@|||||||||||||||
$sql_id,        $min_last_load_time, $buffer_gets
@<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
$blurb
.
    open REPORT, '>', \$report;
    for my $aref (@foo) {
        ($sql_id, $min_last_load_time, $buffer_gets) = @$aref;
        write REPORT;
    }
    close REPORT;
    return $report;
}

There are more modern ways of achieving the same end. While format is useful for the odd quick-and-dirty script here and there I wouldn't recommend spending large amounts of time or effort getting into the details. If you need this sort of thing often, consider something like Template as a better investment.

[gandolf989]

That helped. Unfortunately I am not getting buffer overflow errors. The total size of the email is 40k. So I don't see why I would get a page overflow error. Any ideas?

INFO - Opening DBI connection
page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126
+.
 at /home/oracle/scripts/findMostExpensiveSQL.pl line 126.
        findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7
+00), "\x{a}SELECT * FROM (\x{a}SELECT sql_id,    TO_CHAR(MIN(last_loa
+d_time"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li
+ne 252
page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126
+.
 at /home/oracle/scripts/findMostExpensiveSQL.pl line 126.
        findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7
+00), "\x{a}SELECT * FROM (\x{a}SELECT sql_id,    TO_CHAR(MIN(last_loa
+d_time"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li
+ne 252
page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126
+.
 at /home/oracle/scripts/findMostExpensiveSQL.pl line 126.
        findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7
+00), "\x{a}SELECT * FROM (\x{a}SELECT t.sql_id, TO_CHAR(MIN(t.last_lo
+ad_tim"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li
+ne 260
page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126
+.
 at /home/oracle/scripts/findMostExpensiveSQL.pl line 126.
        findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7
+00), "\x{a}SELECT * FROM (\x{a}SELECT t.sql_id, TO_CHAR(MIN(t.last_lo
+ad_tim"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li
+ne 260
page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126
+.
 at /home/oracle/scripts/findMostExpensiveSQL.pl line 126.
        findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7
+00), "\x{a}SELECT * FROM (\x{a}SELECT t.sql_id, TO_CHAR(MIN(t.last_lo
+ad_tim"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li
+ne 260
page overflow at /home/oracle/scripts/findMostExpensiveSQL.pl line 126
+.
 at /home/oracle/scripts/findMostExpensiveSQL.pl line 126.
        findMostExpensiveSQL::print_query_results(DBI::db=HASH(0x2c0d7
+00), "\x{a}SELECT * FROM (\x{a}SELECT t.sql_id, TO_CHAR(MIN(t.last_lo
+ad_tim"...) called at /home/oracle/scripts/findMostExpensiveSQL.pl li
+ne 260

sub print_query_results {
   my $dbh                = shift;
   my $sql_query          = shift;
   my $sql                = SQL::Beautify->new;
   my ( $sql_id, $min_last_load_time, $buffer_gets, $disk_reads, $exec
+utions, $sorts, $parse_calls, $sql_fulltext );
   my $return_scalar      = qq{
};

   my $sth = $dbh->prepare( $sql_query );
   my $formatted_sql;

   $sth->execute();
   $sth->bind_columns( undef, \$sql_id, \$min_last_load_time, \$buffer
+_gets, \$disk_reads, \$executions, \$sorts, \$parse_calls, \$sql_full
+text );

format OUTPUT =
@|||||||||||||||@||||||||||||||||||||@|||||||||||||||@|||||||||||||||@
+|||||||||||||||@|||||||||||||||@|||||||||||||||
'SQL_ID',       'min_last_load_time','buffer_gets',  'disk_reads',   '
+executions',   'sorts',        'parse_calls',
@<<<<<<<<<<<<<<<@>>>>>>>>>>>>>>>>>>>>@>>>>>>>>>>>>>>>@>>>>>>>>>>>>>>>@
+>>>>>>>>>>>>>>>@>>>>>>>>>>>>>>>@>>>>>>>>>>>>>>>
$sql_id,        $min_last_load_time, $buffer_gets,   $disk_reads,    $
+executions,    $sorts,         $parse_calls,

@*
$sql_fulltext

.

   open OUTPUT, '>', \$return_scalar;
   while( $sth->fetch() ) {
      $formatted_sql = SQL::Beautify->new;
      $formatted_sql->query( $sql_fulltext );
      $sql_fulltext = $formatted_sql->beautify;;
      OUTPUT->flush();
      write OUTPUT;
   }
   close OUTPUT;

   return $return_scalar;
}

2018-09-22 Athanasius added code tags

[poj]

Consider separating the data extraction from the formatting so you can prove each part individually. Also, maybe sprintf is all you need.

my $result = get_query_results($dbh,$sql);
print format_query_results($result);

sub get_query_results {
   my ($dbh,$sql_query) = @_;
   my $sth = $dbh->prepare( $sql_query );
   $sth->execute();
   my $ar = $sth->fetchall_arrayref;
   return $ar;
}

sub format_query_results {
   
   my $ar = shift;
   
   my $fmt = "%-15s %20s %15s %15s %15s %15s %15s\n";
   my $heading = sprintf $fmt, qw(SQL_ID min_last_load_time 
           buffer_gets disk_reads  executions  sorts parse_calls );
   
   my $report;   
   for my $rec ( @$ar ){
     $report .= $heading;
     $report .= sprintf $fmt, @$rec[0..6];

     my $sql = SQL::Beautify->new( query => $rec->[7] );
     $report .= "\n".$sql->beautify."\n";
   }
   return $report;
}

poj

[gandolf989]

Hi Poj, That was exactly what I needed. I am no longer getting page overflows. Thanks!

[hippo]

The total size of the email is 40k. So I don't see why I would get a page overflow error. Any ideas?

From perldiag:

page overflow

(W io) A single call to write() produced more lines than can fit on a page. See perlform.

Either split the write up or increase the page size.

[BillKSmith]

Be warned, this is the reference to consult when all else fails. It will answer any floating point question, but at the expense of being "more than you care to know". Its math is not difficult, but the large amount of it can be frustrating. I do recommend that you skim through it to at least learn what it covers.