Perl, Oracle, DBI, and selecting LOB data

Awhile back I wrote about the Perl DBI and MS SQL text fields and according to google analytics, it was a fairly popular post. Today I was reminded just why that is - dealing with LOB type fields is a pain in the butt! This time around, I just needed to get some XML back out of an Oracle table for additional processing. For whatever reason, Oracle makes this (in my opinion) much harder than it really needs to be. And to make things worse, a quick google search did not reveal any simple, or complete, solutions! So, after searching around for awhlie without finding a solid, simple solution I decided I should just bite the bullet and update my MS SQL example to also work with Oracle. The biggest advantage to this approach is that it’s (more or less) driver independant. Meaning, that you don’t really have to mess around with all kinds of driver specific features or options like ora_lob_read! So without further ado, here’s some working code as an example:

$dbh->{‘LongTruncOk’} = 1; $dbh->{'LongReadLen’} = 255; my $sth = $dbh->prepare('SELECT id, length(xml_string) dlength, xml_string FROM mytable’) or die; $sth->execute() or die; my $xml = “”; while (my @data = $sth->fetchrow_array()) {   my $xml = “”;   if ($data[1] > 255) {     my $offset = 1;     while ($offset < $data[1]) {       my $newoffset;       if ($offset + 255 < $data[1]) { $newoffset = 255; } else { $newoffset = ($data[1] - $offset) + 1; }       my $query = 'SELECT SUBSTR(xml_string, ’ . $offset . ’, ’ . $newoffset . ’) AS txt FROM mytable where id = ’ . $data[0];       my $st1 = $dbh->prepare($query) or die “problems”;       $st1->execute() or die;       while (my @d1 = $st1->fetchrow_array()) {         $xml .= $d1[0];       }       $offset += 255;     }   } else {     $xml .= $data[2];   }   print $xml . “\n\n”; }
Don’t forget you still need your db connection stuff before all of this (and you will need to require whatever database library you are using – when testing this I was using DBD::Oracle). Also don’t forget that this solution is not paticularly effecient (so try to limit it in production situations). Still, it works (at least for what I needed) and I think it’s simple enough. Hopefully it can help save a few other people some time (and headaches) too.

This post has received 42 loves.


ARCHIVE OF POSTS
see more archived posts »


This is the personal blog of Kevin Marshall (a.k.a Falicon) where he often digs into side projects he's working on for digdownlabs.com and other random thoughts he's got on his mind.

Kevin has a day job as CTO of Veritonic and is spending nights & weekends hacking on Share Game Tape. You can also check out some of his open source code on GitHub or connect with him on Twitter @falicon or via email at kevin at falicon.com.

If you have comments, thoughts, or want to respond to something you see here I would encourage you to respond via a post on your own blog (and then let me know about the link via one of the routes mentioned above).