http://www.perlmonks.org?node_id=1222723

gandolf989 has asked for the wisdom of the Perl Monks concerning the following question:

I am writing a perl script to take SQL stats for queries in my Oracle database and output the results in a formatted way to a scalar, so that I can send an email. I found this format syntax, but I am not sure if it is still supported or if I am using it correctly. I just want to make the formatting easy to read, so that I can send it to the developers and they can see the worst SQL running in the Oracle database. Is there another way that I should write this code? Or do I just need to fix the syntax.
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 ); $sth->execute(); $sth->bind_columns( undef, \$sql_id, \$min_last_load_time, \$buffer +_gets, \$disk_reads, \$executions, \$sorts, \$parse_calls, \$sql_full +text ); while( $sth->fetch() ) { 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->query( $sql_fulltext ) . open OUTPUT, '>', \$return_scalar; write OUTPUT; close OUTPUT; } return $return_scalar; }

Replies are listed 'Best First'.
Re: I need outputting the results of a query in a formatted way to a scalar, so that I can email the results
by hippo (Bishop) on Sep 20, 2018 at 15:57 UTC
    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.

      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

        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
        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.

        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.
        Bill