Description:
------------
There is a memory leaking when using the OCI8 interface and querying 
XML columns. Demo code available via the url below. This creates a 
table with an XML column and queries this column. UGA memory is 
leaking. This does not happen when doing the same directly via 
SQLPlus.

Reproduce code:
---------------
http://oberon.q-one-hosting.com/6648051.txt

Expected result:
----------------
No UGA memory leaking

Actual result:
--------------
UGA memory going up. Table with temporary lobs filling up.

Confirmed.

Would pay someone who resolves this bug. Feel free to contact me if you
are interested.

This is really an issue with temporary LOBS since getClobVal()
returns a temporary LOB.

There are two parts to the fix: changing the script and patching
the OCI8 extension.  Also don't forget to apply the patch for
http://bugs.php.net/bug.php?id=42496

Please test this suggestion and report any issues.

Thanks to Krishna & Shankar for the solution.

1. Change the test to get the results as LOBs, not as
strings. This allows the script to free temporary LOBs.

In the supplied testcase change:

    $query = "select extract(xml, '/').getclobval() from ugatest";
    $stmt = oci_parse($conn, $query);
    if (oci_execute($stmt))
        while ($result = oci_fetch_array($stmt,
OCI_NUM+OCI_RETURN_LOBS))
            ;

to:

    $query = "select extract(xml, '/').getclobval() from ugatest";
    $stmt = oci_parse($conn, $query);
    if (oci_execute($stmt))
        while ($result = oci_fetch_array($stmt, OCI_NUM)) {
            // echo $result[0]->load(), "\n"; // do something with the
XML
            $result[0]->free();  // free the temporary LOB
        }

The connection must be open when LOB->free() is called, as the
underlying OCILobFreeTemporary() call does a roundtrip to the
database.

2.  Patch oci8_lob.c.  The change copies some LOB freeing code
from php_oci_lob_close() into php_oci_lob_free():

--- oci8_lob.c.orig    2007-07-31 12:21:08.000000000 -0700
+++ oci8_lob.c    2007-12-27 12:33:19.000000000 -0800
@@ -647,6 +647,9 @@
  Close LOB descriptor and free associated resources */
 void php_oci_lob_free (php_oci_descriptor *descriptor TSRMLS_DC)
 {
+#ifdef HAVE_OCI8_TEMP_LOB
+    int is_temporary;
+#endif
 
     if (!descriptor || !descriptor->connection) {
         return;
@@ -662,6 +665,40 @@
         php_oci_lob_flush(descriptor, OCI_LOB_BUFFER_FREE TSRMLS_CC);
     }
 
+#ifdef HAVE_OCI8_TEMP_LOB
+    if (descriptor->type == OCI_DTYPE_LOB) {
+        PHP_OCI_CALL_RETURN(descriptor->connection->errcode,
+                OCILobIsTemporary,
+                (
+                    descriptor->connection->env,
+                    descriptor->connection->err,
+                    descriptor->descriptor,
+                    &is_temporary
+                 )
+        );
+        if (descriptor->connection->errcode != OCI_SUCCESS) {
+            php_oci_error(descriptor->connection->err,
descriptor->connection->errcode TSRMLS_CC);
+            PHP_OCI_HANDLE_ERROR(descriptor->connection,
descriptor->connection->errcode);
+            return 1;
+        }
+        if (is_temporary) {
+            PHP_OCI_CALL_RETURN(descriptor->connection->errcode,
+                    OCILobFreeTemporary,
+                    (
+                        descriptor->connection->svc,
+                        descriptor->connection->err,
+                        descriptor->descriptor
+                     )
+            );
+            if (descriptor->connection->errcode != OCI_SUCCESS) {
+                php_oci_error(descriptor->connection->err,
descriptor->connection->errcode TSRMLS_CC);
+                PHP_OCI_HANDLE_ERROR(descriptor->connection,
descriptor->connection->errcode);
+                return 1;
+            }
+        }
+    }
+#endif
+
     PHP_OCI_CALL(OCIDescriptorFree, (descriptor->descriptor,
descriptor->type));
 
     zend_list_delete(descriptor->connection->rsrc_id);

Really great! Thanks a lot!! This patch works. What I don't understand:
I thought OCI_RETURN_LOBS is just a short-cut for those who don't want
to write:

$s=$result[0]->load();
$result[0]->free();
$result[0]=$s;

If you use OCI_RETURN_LOBS you dont want to care about lobs but get the
result as a string and forget about lobs altogether. So IMHO this should
work as well. My specific problem is solved though.

>What I don't understand: I thought OCI_RETURN_LOBS is just a short-
>cut for those who don't want to write:

That's what I don't understand either: does the leak appear only on
per-session basis or Oracle doesn't free those LOBs at all?
If the leak is only per-session, then users are not supposed even to
notice it, since PHP requests are not supposed to take more than several
seconds.

Temporary LOBs are created in UGA memory. This is per-session, so 
the leak appears on a per-session basis. Nevertheless this is a 
problem, because PHP scripts dont necessarily have to run for a few 
seconds. PHP is a full-featured scripting language and can also be 
used from the command-line or to implement longer-running 
import-scripts. Even if not, the limit is quickly reached, when 
reading many rows like in my example.

An enhanced patch was sent to the bug filer and Tony on 8th Jan.  I'm
still waiting for feedback . . .

Sorry. I have been on vacation. I will try to check the new patch 
this week if possible. Definitely next week otherwise.

This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

+----------------------------------------------------------
| Fixed in 5.2.6, 5.3, 6.  Queries with OCI_RETURN_LOBS will
| automatically free the DB's Temporary LOBs.  Fetches
| returning LOB locators need to use LOB->free().  See
| oci8/tests/bug43497.phpt for examples.
+-----------------------------------------------------------

Confirmed. Fixed. OCI_RETURN_LOBS are freed automatically now. 
Thanks a lot to everyone involved!