Christopher Jones on OPAL

Temporary LOBS: Sometimes when you query a large dynamic string, Oracle needs to create a "temporary LOB" in the database to hold the data.  For example, this happens when querying EXTRACT(...).GETCLOBVAL() which converts XML query results to a LOB.  With a temporary LOB, interfaces like PHP's OCI8 extension can fetch chunks or stream from it just like they fetch from a "persistent LOB" (i.e. a LOB column).  When PHP is done with the temporary LOB, it needs to tell Oracle to destroy it. If this isn't done, then the temporary LOB will hang around using DB space until the connection is closed.

I just merged a fix worked on by Krishna Mohan and myself for bug 43497.  The DB's temporary LOB is freed at point of use when OCI_RETURN_LOBS is specified:

    $stid = oci_parse($c, 
            "select extract(xml, '/').getclobval() from mytab");
    oci_execute($stid);
    while ($result = oci_fetch_array($stid, OCI_RETURN_LOBS)) {
        echo $result[0];  // string result
    }

This reduces DB memory use while the script is executing.

The fix also allows you to free the temporary LOB explicitly when you fetch the data using a LOB locator:

    $stid = oci_parse($c, 
            "select extract(xml, '/').getclobval() from mytab");
    oci_execute($stid);
    while ($lob = oci_fetch_array($stid)) {
        $result = $lob[0]->load();  // get string from the locator
        echo $result;
        $lob[0]->free();
    }

The fix is available in PHP's CVS snapshots . There is a testcase in oci8/tests/bug43497.phpt if you want to see a running example.

Using Instant Client: In other fix news, after some effort and patience, I merged installation support for Oracle provided 64bit Instant Client RPMs packages, which have a slightly funky directory structure. Tony Dovgal and I worked on the PHP side of this.

At the same time I patched the configuration script to use Instant Client RPMs if the option --with-oci8=instantclient is used without an explicit directory.  If you don't have RPMs, or want to force the Instant Client directory, continue using --with-oci8=instantclient,/path/to/oci/lib

On Tuesday I went to the San Francisco geekSession to hear about PHP Scalability, Performance, and the Future .  The brief talk format worked well, letting four different viewpoints be heard.  There was an all-star cast (we have many stars in the PHP world).  I heard the comment later that this geekSession had the most notebooks & pens out so far.  There were certainly some gems worth noting.  I met a bunch of people and found the evening very valuable, as well as enjoyable.

Wednesday I was at an Oracle-internal meeting where Mark Douglas of eHarmony (not a PHP site) presented on their architecture and how they use Oracle.  They migrated to Oracle a few years ago.  They actively search for new technologies to gain a competitive edge and make efficient use of that technology.  With Oracle they don't need to worry about sharding or the like - they let the DB handle that for them. With half a billion page views a month they still only need two DBAs.  In the PHP world I too often see wheels being reinvented, or the language being or used to do operations that have already been solved and are better handled elsewhere.

Today, with a different crowd again, I finally managed to make it to a Lunch 2.0.  A nice sunny day saw us on the roof top at e-Storm, "an interactive marketing and advertising company".  The attendees were a good mix of tech and business folk and it was a great (if all too brief) way to meet.  What do you do at a Lunch 2.0?  Eat lunch really.  There was a brief announcement about e-Storm's services and some fun Pleo's to pat, but the networking was the key thing. It was like the best bits of a conference without having to attend any lectures.

PS And for something that I realize in the OOW organized chaos, I didn't blog about, check out RASMUS LERDORF ENTERPRISE DEVELOPER OF THE YEAR.

PPS The next SF PHP event is the PHP Meetup on 7th February at C|NET. It's free and fun.