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
