byterock

In part 14 we had a look at a look at blob, lob and clobs and even memo data sometimes DBD developers add in a few goodies that are outside the spec despite the ever watchful eyes of the DBI gods. Here is one exmple.

XML, That will never catch on we have SML

On neat little trick, besides a bunch of others, that DBD::Oracle can do is suck up large XML documents into its native XMLType.

It is actually quite easy as all that is really necessary is to import the correct data type and then bind it to the appropriate column.

So to start

use DBD::DBD qw(:ora_types);

and then simply bind it with the ORA_XMLTYPE like this

my $sql = "insert into big_data values(:p_xml)"; my $sth =$dbh-> prepare($SQL); $sth-> bind_param(":p_xml", $big_hunk_of_xml, { ora_type => ORA_XMLTYPE }); $sth-> execute();

And that's it.

Let Your Fingers Do the Walking

The general guideline when using perl, DBI, and XML it to let the database manage data and to transfer the minimum amount of data across the network. In other words let the DB to the markup for you rather than your perl. Fortunately Oracle has the DBMS_XML_GEN package that does that nicely for us.

All we have to do it call the the PL/SQL package DBMS_XMLGEN and return the XML as a CLOB column, which as we both know DBD::Oracle can easily handle.

Something like this;

$dbh->{LongTruncOk} = 0;
$dbh->{LongReadLen} = 2*1024*1024; # 2MB limit for displaying LOG
$sql = "SELECT dbms_xmlgen.getxml('
SELECT last_name, first_name
FROM employees
') xml
FROM dual";

$sth = $dbh->prepare($sql);
$sth->execute();
my ($r_xml) = $sth->fetchrow();

and now perl can play with $r_xml. No funny parsing errors or xml encoding nightmares just plain old well formatted XML.