# 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);
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. |