Re3: Sqlplus -> DBD::Oracle Question

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.

Comment on Re3: Sqlplus -> DBD::Oracle Question Download Code

Replies are listed 'Best First'.
Re: Re3: Sqlplus -> DBD::Oracle Question by set_uk (Pilgrim) on Apr 10, 2003 at 15:09 UTC
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.	[reply]
Re5: Sqlplus -> DBD::Oracle Question by dragonchild (Archbishop) on Apr 10, 2003 at 15:49 UTC
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.	[reply]