Re^2: Parsing Oracle PIVOT XML data

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.

Comment on Re^2: Parsing Oracle PIVOT XML data Download Code

Replies are listed 'Best First'.
Re^3: Parsing Oracle PIVOT XML data by poj (Abbot) on Jan 18, 2016 at 17:03 UTC
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	[reply] [d/l]
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.	[reply]
Re^5: Parsing Oracle PIVOT XML data by Corion (Patriarch) on Jan 19, 2016 at 11:37 UTC
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;` [download] 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.	[reply] [d/l]


Do you know where your variables are?
	PerlMonks