Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Re^3: Parsing Oracle PIVOT XML data

by poj (Abbot)
on Jan 18, 2016 at 17:03 UTC ( #1153028=note: print w/replies, xml ) Need Help??


in reply to Re^2: Parsing Oracle PIVOT XML data
in thread Parsing Oracle PIVOT XML data

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

Replies are listed 'Best First'.
Re^4: Parsing Oracle PIVOT XML data
by tweetiepooh (Hermit) on Jan 19, 2016 at 10:32 UTC

    Thanks. That will work as a workaround and possibly a final solution. One issue I would have is that the account used to create reports can't create tables. I can get round it but it lacks some of the flexibility with lots of reports running at same time.

    It's just interesting as to what is getting returned parsed by DBI or DBD::Oracle from the original. I can't use the SQL SELECT to create a table so it's not returning a "simple" value.

      I would assume that creating temporary tables is possible even if the user lacks the permissions to create (permanent) tables:

      CREATE GLOBAL TEMPORARY TABLE my_temp_table (...) ON COMMIT DELETE ROWS;

      As to the larger issue, I think it's mainly an issue of the DBD to return the structured data from the XML query, and maybe supporting that isn't that easy without the driver becoming "locked" to a specific version of Oracle.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1153028]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (4)
As of 2022-09-30 10:19 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    I prefer my indexes to start at:




    Results (126 votes). Check out past polls.

    Notices?