Sorry "$l_cnt=0" is missing from the code. It should read:

$l_res=oci_new_connect("<username>","<password>","<sid>");
oci_execute($l_st);
$l_cnt=0;
while(1){
	$l_cnt++;
        $l_st=oci_parse($l_res,"select * from tblDocuments  where
id=$l_cnt ");

	oci_execute($l_st);	
        $l_row=oci_fetch_row($l_st);
	oci_free_statement($l_st);
	echo $l_cnt,':';print_r($l_row);
}

I can reproduce the problem with your script.

OS: Scientific  Linux 4.x
Server: Oracle9i 9.2.0.7.0 on different machine.
Client: Oracle instant client 10.2 
PHP: 5.2.4 compiled from source (--with-oci8=shared,$ORACLE_HOME ) 

With my previous version 5.1.6 the problem is NOT reproducable.

Sorry, the build is --with-oci8=instantclient,$ORACLE_HOME

I can reproduce the problem as well.

OS:  RedHat 4 Enterprise ES
Server: Oracle9i Enterprise Edition Release 9.2.0.7.0
Client: Oracle instant client 10.2 
PHP: 5.2.2 compiled from source (--with-oci8=instantclient,$ORACLE_HOME )

We experience this bug to a further extend than described before:

- Selecting 2 clobs in one query fails (SELECT c1, c2 FROM c_table)
- Selecting the same clob twice in the same query fails (SELECT c1, c1 from c_table)
- Selecting more than one row with a clob fails (SELECT c1 FROM c_table WHERE ROWNUM < 10)
- Selecting one row with clob WORKS (SELECT c1 FROM c_table WHERE ROWNUM <= 1)

Narrowed down the problem to the OCI8 driver, which is versioned separately from PHP. After a bit of testing, the problem appeared in v1.2.4 of OCI8, which got included in PHP v5.2.3

OCI8 v1.2.3 does not exhibit the problem. vOCI8 1.3.0 Beta (as of today) still exhibits the problem. I've reported it on the bugtracker here http://pecl.php.net/bugs/bug.php?id=12407 linking back to this bug.

The different versions of the OCI8 driver are downloadable from http://pecl.php.net/package/oci8 -- HTH!

These are the revisions as reported in phpinfo() for the versions I've tested:

grep -r Revision oci8-1.*/oci8.c 
oci8-1.1.1/oci8.c:      php_info_print_table_row(2, "Revision", "$Revision: 1.274 $");
oci8-1.2.3/oci8.c:      php_info_print_table_row(2, "Revision", "$Revision: 1.269.2.16.2.29 $");
oci8-1.2.4/oci8.c:      php_info_print_table_row(2, "Revision", "$Revision: 1.269.2.16.2.38 $");
oci8-1.3.0/oci8.c:      php_info_print_table_row(2, "Revision", "$Revision: 1.269.2.16.2.38.4.1 $");

This Moodle bugreport is related: http://tracker.moodle.org/browse/MDL-11429

Note that under moodle (using AdoDB) we are seeing the problem as soon as the resultset contains at least one LOB. If the table has LOBs but the resultset is empty, no cursors are leaked.

Does setting oci8.statement_cache_size = 0 change the behavior?

> Does setting oci8.statement_cache_size = 0 change the behavior?

It does not in our tests, unfortunately.

We can reproduce the problem with OCI8 versions before 1.2.4: Version 1.2.3, $Revision: 1.269.2.16.2.32 $

I can confirm that 1.2.4 has the bug, but 1.2.3 ($Revision: 1.269.2.16.2.30 $) doesn't have it.

Saludos. I?aki.

What i see after upgrading to PHP 5.2.5:
NOT Working: oci8 1.2.4,$Revision: 1.269.2.16.2.38 $
Working    : oci8 1.2.3,$Revision: 1.269.2.16.2.29 $

I'm using OCI8 1.2.4 with Oracle 11g. A previous version doesnt seem to work, so I cannot test with 1.2.3. It also says so in the changelog for 1.2.4: Add Oracle 11g support. Now, whenever I select (c)lobs (even with only 1 lob column),, the table v$temporary_lobs keeps filling up and UGA memory is consumed for each row that's being read until the server aborts with an out-of-memory error. This does not happen when I run my statements directly via SQLplus, so it seems to be an OCI8/PHP bug. So, is this related to this bug or should I file a new one?

I recognized, that when calling oci_free_statement() for every lob column that is returned by the select, the cursor gets closed correctly.
So if i have three lob columns in the query, i have to call oci_free_statment() three times on the statement handle to have it closed correctly.

This was reproduced with 5.2.3 on Linux.
Please try this patch AND LET US KNOW THE RESULT - thanks!

In php_oci_define_callback function [oci8_statement.c],
zend_list_addref is called for every lob column of each row.  When we
commented out this increment, the statements were destroyed and no
cursor leaks were seen.

case SQLT_RDD:
case SQLT_BLOB:
case SQLT_CLOB:
case SQLT_BFILE: {
...
descr = php_oci_lob_create(outcol->statement->connection, dtype TSRMLS_CC);
               if (!descr) {
                   return OCI_ERROR;
               }
               /*zend_list_addref(outcol->statement->id); Commented out */

Thanks, this fix works for lobs, but i investigated a bit further and realized, that the same problem occures when returning cursors. 
To totally remove the problem you would have to remove "zend_list_addref(outcol->statement->id);" from SQLT_RSET in php_oci_define_callback() also. 
I don't know if this is the right solution at all, i'm not that much into the php-engine, oci8-library and C in general. Wouldn't it be better to close all related cursors when closing the "main"-cursor instead of not registering them?

Same problem here: php 5.2.5 with oracle 11g client (linux 64bit) against oracle 10.2.0.3 server (solaris 10 sparc 64bit).
Patch works ok, no ORA-01000 errors anymore.

I tried to remove "zend_list_addref(outcol->statement->id);" from SQLT_RSET in php_oci_define_callback function() as well, but that doesn't work really well. It seemed to work at first, but it creates problems when returning the cursor from a function, because it leads to "ORA-01001: invalid cursor" when i try to fetch from the returned cursor.

Testcase:
<?php
 
$conn = ocilogon('user', 'pass', 'db');
 
function fetch($conn, $id)
{
    $result = null;
    $stmt = ociparse($conn, 'select cursor(select * from dual) c from dual');
    ociexecute($stmt, OCI_DEFAULT);
    ocifetchinto($stmt, $result, OCI_ASSOC);
    ociexecute($result['C'], OCI_DEFAULT);
    return $result['C'];
}

for ($id = 1; $id <= 300; $id++) {
    $cur = fetch($conn, $id);
    ocifetchinto($cur, $row, OCI_ASSOC);
    ocifreestatement($cur);    
}
?>

When i run the code above without removing "zend_list_addref(outcol->statement->id);" from SQLT_RSET i have the problem, that i get an "ORA-01000: maximum open cursors exceeded", because the statement within the function doesn't get closed. 

I don't know if that can be implemented easily, but the best thing would be, that a cursor gets closed if it is already out of scope and therefore not accessible from the php-code anymore and all nested cursors got closed already.

Thanks for the information.  Can you please open a new bug for the result set issue.  Let's use this (#42496) bug only for any issues with the LOB fix.

Ok, see Bug #43492 for the result set issue

Had the same problem (maximum number of open cursors reached when 
selecting CLOBs) with PHP 5.2.4 and 5.2.5 on several boxes.

Example: PHP 5.2.4 (phpinfo says OCI8 version 1.2.3, Revision: 
1.269.2.16.2.37) on Linux (Debian 4.0), compiled against Oracle Instant 
Client 10.2.0, connected to Oracle 10.1.0.4.0.

The patch (commenting out zend_list_addref() in oci8_statement.c) seems 
to work; at least I cannot reproduce the error any longer.

We did not test the patch in production yet, so I cannot say whether 
there are any side effects.

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.

Fix has been merged to CVS. Thanks to Haneef for the investigation & patch.