Where's your Perl code?

The Perl side of this is pretty simple

# connect to the database and set date format
# for these reports dates are likey truncated somehome
my $dbh = connectdb();
$dbh->do("alter session set nls_date_format='dd Month yyyy hh24:mi:ss'
+");

# uncomment the following for long running reports
$dbh->do("alter session disable parallel query");
$dbh->do(qq(alter session set "_b_tree_bitmap_plans"=false));
$dbh->do("alter session set db_file_multiblock_read_count=2048");

# compile the SQL
my $sth = $dbh->prepare(SQL);

# run the query
$sth->execute();

# retrieve the names of the columns returned and write header line
my @names = @{$sth->{NAME}};

# create a hash based on the first column
my %data = %{$sth->fetchall_hashref($names[0])};
print Dumper(%data);
[download]

What I'd essentially expect is 2 strings, one the key and the second the XML. I'd then parse the XML to get my columns and fill the data in. The intention is to have this as a simple template so anyone can copy the file, edit the SQL (just changing limits, and data items) and the report would generate what's needed. I already have this for basic list reports.

The issue seems that somewhere in the data line the XML shown as a string in SQLPlus is getting converted somehow.

A work around might be to use a temporary table

my $sql = '
insert into TMP_XML value ( 
  select * from (
    select f1,f2,t from table
    where date > sysdate - 2
  )
  pivot xml (
    count(t) as al, sum(t) as ev
    for f2 in (any)
  ) 
)';
$dbh->do($sql);

my $sth = $dbh->prepare('SELECT * FROM TMP_XML');
$sth->execute();
[download]

poj