break on country skip 1
compute sum of downtime on country

define start_date ="trunc((get_date('MONTH','START') - 1),'MONTH') + 1
+2/24"
define end_date="trunc(trunc(get_date('MONTH','START'),'MONTH'),'DD')+
+ 12/24"
define percent="((1 - (sum(NVL(ncl.downtime_secs,0))/sum(date_seconds_
+between(&end_date,&start_date)))) * 100)"

spool ${spool_file}
set markup html on spool on
select  sd.site_name,
    sum(NVL(ncl.downtime_secs,0)) downtime,
    (case when &percent < 100 then to_char(&percent,99.99999) else to_
+char(&percent,999) end) availability,
    convert_seconds_to_dhms(sum(NVL(ncl.downtime_secs,0))) downtime_dh
+ms
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 &percent < 100
group by sd.site_name
order by downtime
[download]

Don't think about this as converting a script line for line. Think about this as transporting functionality. Instead of doing everything in SQL, do some of it in Perl. For example:

    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]

At the end of that snippet, you have a hash, keyed by country with values of the stuff returned as well as the total downtime.

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.

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]

And of course, spool spoolfile is just open OUT, ">spoolfile"; select OUT; or something like that.

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

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