http://qs1969.pair.com?node_id=1152861

tweetiepooh has asked for the wisdom of the Perl Monks concerning the following question:

Hi fellow monks

I am trying to execute the below query

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

using DBI. The query in SQLPlus runs fine and I get no errors in Perl. f1 returns fine but the xml portion seems to be an empty array. I am using fetchall_hashref.SQLPlus output

f1 -- f2_xml -------------------------------------------- 99999 <PivotSet><item><column name = "F2">0</column><column name = "AL">12</ +c olumn><column name = "EV">169</column></item></PivotSet>

Using Data::Dumper I see data like

varnn = { 'f1' = '99999'. 'f2_xml' = [] };

It's how I get to f2_xml I need a little help with. Thanks

Replies are listed 'Best First'.
Re: Parsing Oracle PIVOT XML data
by SimonPratt (Friar) on Jan 15, 2016 at 16:50 UTC

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

      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();
        poj
Re: Parsing Oracle PIVOT XML data
by Anonymous Monk on Jul 27, 2018 at 16:14 UTC
    select f1, xmltype.getclobval(f2_xml) as f2_xml from ( 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) ) )