my $sql = <<SQL;
select  sd.site_name,
    sum(NVL(ncl.downtime_secs,0)) downtime,
    (case when ? < 100 then to_char(?,99.99999) else to_char(?,999) en
+d) availability,
    convert_seconds_to_dhms(sum(NVL(ncl.downtime_secs,0))) downtime_dh
+ms,
    country
from    ncl_summary_tmp ncl,
    (select distinct contracted_node_name, site_name from contracted_n
+odes ) cn,
    site_details sd
where   UPPER(SUBSTR(ncl.node (+) ,0,(INSTR(ncl.node (+),'.') -1))) = 
+cn.contracted_node_name 
and    sd.site_name = cn.site_name
having ? < 100
group by sd.site_name
order by downtime
SQL

    my $sth = $dbh->prepare_cached($sql) || die;
    $sth->execute(($percent) x 4) || die;

    $sth->bind_columns(\my (
        $sitename,
        $downtime,
        $availability,
        $downtime_dhms,
        $country,
    ));

    my %countries;
    while ($sth->fetch)
    {
        push @{$countries{$country}{values}}, [
            $sitename,
            $downtime,
            $availability,
            $downtime_dhms,
        ];
        $countries{$country}{total_downtime} += $downtime;
    }

    $sth->finish;
[download]

I'm curious as to what more you'd need ...

------
We are the carpenters and bricklayers of the Information Age.

Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

I guess what I want is all the functionality of SQLPlus in Perl in as few lines of code.

As good as it is, using the approach you lay out above I'll have to write a perl sub for each sql query to further refine the report.

I am trying to create a setup which holds all the report definitions in XML and pulls them in and processes via generic code.

This approach requires query specific perl code to function.

You have report layouts in XML and you want to use Perl for that. Ok ... this is important info.

From what I know, there is no single templating system that will map XML layouts to SQL queries and produce HTML. However, you can glue together a few different components and come up with a generic solution. However, it won't be just a "few lines of code". My best guess would be that you would have to look at redesigning the system.

However, the benefit is that you're not limited to just what you could do in SQLPlus. For example, once you have your templates in HTML::Template, a few minor changes and you can use PDF::Template to output the reports in PDF. No code changes required. Outputting to Excel format is also similarly easy (though there is no XLS::Template ... yet).

Of course, and as always, YMMV.

------
We are the carpenters and bricklayers of the Information Age.

Don't go borrowing trouble. For programmers, this means Worry only about what you need to implement.

Please remember that I'm crufty and crochety. All opinions are purely mine and all code is untested, unless otherwise specified.

The SQL can stay like it is, and the session variables you have will map directly into Perl scalars. As you noted, you can either incorporate them directly into the SQL, or make separate calls like you are now to populate them, then bind or interpolate them into the main SQL (although I don't see where you're using start_date and end_date).

The break and compute you'll have to do by yourself. There are different ways, but a simple outline may be:

my $lastcountry = '';
my $totaldowntime = 0;
while (my $row = $sth->fetch()) {
    (..., $country, $downtime, ...) = @$row;
    $totaldowntime += $downtime;
    if ($country ne $lastcountry) {
         # note this will be true first time thru the loop
         print $totaldowntime;
         $totaldowntime = 0;
         next_page();    # however you accomplish this in your setting
    }
[download]

What's going to take work is "set markup html". I've never used it, but I presume it's got some sort of automated layout, like a table or something. You'll have to cook up the layout yourself, but the good news is that you have full control over the output, and you can use tools like HTML::Template or Template::Toolkit to make the job easier. (I'd recommend the former if you've never used HTML templates before -- it's easier to get started.)

HTH

It does help - thanks