AFAIK, you cannot. Session variables, breaks, etc., are part of the report-writing features of SQL*Plus. What DBI and DBD::Oracle give you is the ability to make SQL calls to the database, but they don't replicate SQL*Plus functionality. (Think of DBI as "Pro*Perl" and you'll be closer to the true picture.)

That said, remember that you have the full power of Perl at your disposal when you use DBI. For example, formats may be useful for what you're trying to do, and depending on what you're doing with session variables, Perl data structures may be a better substitute.

Maybe more like "Pro*C and all the features you wanted, but were never allowed to have". :-)

------
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 need to recreate the output of the following script:-

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]

The substitution variables I can live without - they just improve code readability. The break and computes I need for the layout of the report

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.

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