#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496 Comment by: tim at digicol dot de Reported By: iddekingej at lycos dot com Status: Open Bug Type: OCI8 related Operating System: win 2000 PHP Version: 5.2.4 New Comment: 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. Previous Comments: [2007-12-04 10:44:12] michael dot virnstein at brodos dot de Ok, see Bug #43492 for the result set issue [2007-12-04 02:13:07] [EMAIL PROTECTED] 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. [2007-11-30 16:36:04] michael dot virnstein at brodos dot de 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: 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. [2007-11-30 16:30:55] michael at six dot de 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. [2007-11-30 10:05:28] michael dot virnstein at brodos dot de 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? The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42496 -- Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496
Comment by: michael dot virnstein at brodos dot de
Reported By: iddekingej at lycos dot com
Status: Open
Bug Type: OCI8 related
Operating System: win 2000
PHP Version: 5.2.4
New Comment:
Ok, see Bug #43492 for the result set issue
Previous Comments:
[2007-12-04 02:13:07] [EMAIL PROTECTED]
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.
[2007-11-30 16:36:04] michael dot virnstein at brodos dot de
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:
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.
[2007-11-30 16:30:55] michael at six dot de
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.
[2007-11-30 10:05:28] michael dot virnstein at brodos dot de
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?
[2007-11-29 18:01:29] [EMAIL PROTECTED]
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
*/
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/42496
--
Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496
Comment by: michael dot virnstein at brodos dot de
Reported By: iddekingej at lycos dot com
Status: Open
Bug Type: OCI8 related
Operating System: win 2000
PHP Version: 5.2.4
New Comment:
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:
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.
Previous Comments:
[2007-11-30 16:30:55] michael at six dot de
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.
[2007-11-30 10:05:28] michael dot virnstein at brodos dot de
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?
[2007-11-29 18:01:29] [EMAIL PROTECTED]
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
*/
[2007-11-29 16:38:45] michael dot virnstein at brodos dot de
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.
[2007-11-22 10:01:34] ghosh at q-one dot com
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?
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/42496
--
Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496
Comment by: michael at six dot de
Reported By: iddekingej at lycos dot com
Status: Open
Bug Type: OCI8 related
Operating System: win 2000
PHP Version: 5.2.4
New Comment:
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.
Previous Comments:
[2007-11-30 10:05:28] michael dot virnstein at brodos dot de
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?
[2007-11-29 18:01:29] [EMAIL PROTECTED]
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
*/
[2007-11-29 16:38:45] michael dot virnstein at brodos dot de
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.
[2007-11-22 10:01:34] ghosh at q-one dot com
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?
[2007-11-15 13:55:42] markus dot knecht at psi dot ch
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 $
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/42496
--
Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496
Comment by: michael dot virnstein at brodos dot de
Reported By: iddekingej at lycos dot com
Status: Open
Bug Type: OCI8 related
Operating System: win 2000
PHP Version: 5.2.4
New Comment:
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?
Previous Comments:
[2007-11-29 18:01:29] [EMAIL PROTECTED]
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
*/
[2007-11-29 16:38:45] michael dot virnstein at brodos dot de
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.
[2007-11-22 10:01:34] ghosh at q-one dot com
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?
[2007-11-15 13:55:42] markus dot knecht at psi dot ch
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 $
[2007-11-13 21:38:44] iarenuno at eteo dot mondragon dot edu
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.
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/42496
--
Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496 Comment by: michael dot virnstein at brodos dot de Reported By: iddekingej at lycos dot com Status: Open Bug Type: OCI8 related Operating System: win 2000 PHP Version: 5.2.4 New Comment: 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. Previous Comments: [2007-11-22 10:01:34] ghosh at q-one dot com 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? [2007-11-15 13:55:42] markus dot knecht at psi dot ch 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 $ [2007-11-13 21:38:44] iarenuno at eteo dot mondragon dot edu 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. [2007-11-09 11:28:26] br at absb dot de We can reproduce the problem with OCI8 versions before 1.2.4: Version 1.2.3, $Revision: 1.269.2.16.2.32 $ [2007-11-09 03:21:13] martin at catalyst dot net dot nz > Does setting oci8.statement_cache_size = 0 change the behavior? It does not in our tests, unfortunately. The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42496 -- Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496 Comment by: ghosh at q-one dot com Reported By: iddekingej at lycos dot com Status: Open Bug Type: OCI8 related Operating System: win 2000 PHP Version: 5.2.4 New Comment: 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? Previous Comments: [2007-11-15 13:55:42] markus dot knecht at psi dot ch 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 $ [2007-11-13 21:38:44] iarenuno at eteo dot mondragon dot edu 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. [2007-11-09 11:28:26] br at absb dot de We can reproduce the problem with OCI8 versions before 1.2.4: Version 1.2.3, $Revision: 1.269.2.16.2.32 $ [2007-11-09 03:21:13] martin at catalyst dot net dot nz > Does setting oci8.statement_cache_size = 0 change the behavior? It does not in our tests, unfortunately. [2007-11-09 01:52:04] [EMAIL PROTECTED] Does setting oci8.statement_cache_size = 0 change the behavior? The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42496 -- Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496 Comment by: markus dot knecht at psi dot ch Reported By: iddekingej at lycos dot com Status: Open Bug Type: OCI8 related Operating System: win 2000 PHP Version: 5.2.4 New Comment: 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 $ Previous Comments: [2007-11-13 21:38:44] iarenuno at eteo dot mondragon dot edu 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. [2007-11-09 11:28:26] br at absb dot de We can reproduce the problem with OCI8 versions before 1.2.4: Version 1.2.3, $Revision: 1.269.2.16.2.32 $ [2007-11-09 03:21:13] martin at catalyst dot net dot nz > Does setting oci8.statement_cache_size = 0 change the behavior? It does not in our tests, unfortunately. [2007-11-09 01:52:04] [EMAIL PROTECTED] Does setting oci8.statement_cache_size = 0 change the behavior? [2007-11-08 23:52:44] martin at catalyst dot net dot nz 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. The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42496 -- Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496 Comment by: iarenuno at eteo dot mondragon dot edu Reported By: iddekingej at lycos dot com Status: Open Bug Type: OCI8 related Operating System: win 2000 PHP Version: 5.2.4 New Comment: 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. Previous Comments: [2007-11-09 11:28:26] br at absb dot de We can reproduce the problem with OCI8 versions before 1.2.4: Version 1.2.3, $Revision: 1.269.2.16.2.32 $ [2007-11-09 03:21:13] martin at catalyst dot net dot nz > Does setting oci8.statement_cache_size = 0 change the behavior? It does not in our tests, unfortunately. [2007-11-09 01:52:04] [EMAIL PROTECTED] Does setting oci8.statement_cache_size = 0 change the behavior? [2007-11-08 23:52:44] martin at catalyst dot net dot nz 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. [2007-11-08 23:38:57] martin at catalyst dot net dot nz 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 $"); The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42496 -- Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496 Comment by: br at absb dot de Reported By: iddekingej at lycos dot com Status: Open Bug Type: OCI8 related Operating System: win 2000 PHP Version: 5.2.4 New Comment: We can reproduce the problem with OCI8 versions before 1.2.4: Version 1.2.3, $Revision: 1.269.2.16.2.32 $ Previous Comments: [2007-11-09 03:21:13] martin at catalyst dot net dot nz > Does setting oci8.statement_cache_size = 0 change the behavior? It does not in our tests, unfortunately. [2007-11-09 01:52:04] [EMAIL PROTECTED] Does setting oci8.statement_cache_size = 0 change the behavior? [2007-11-08 23:52:44] martin at catalyst dot net dot nz 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. [2007-11-08 23:38:57] martin at catalyst dot net dot nz 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 $"); [2007-11-08 23:34:29] martin at catalyst dot net dot nz 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! The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42496 -- Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496 Comment by: martin at catalyst dot net dot nz Reported By: iddekingej at lycos dot com Status: Open Bug Type: OCI8 related Operating System: win 2000 PHP Version: 5.2.4 New Comment: > Does setting oci8.statement_cache_size = 0 change the behavior? It does not in our tests, unfortunately. Previous Comments: [2007-11-09 01:52:04] [EMAIL PROTECTED] Does setting oci8.statement_cache_size = 0 change the behavior? [2007-11-08 23:52:44] martin at catalyst dot net dot nz 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. [2007-11-08 23:38:57] martin at catalyst dot net dot nz 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 $"); [2007-11-08 23:34:29] martin at catalyst dot net dot nz 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! [2007-11-08 16:38:49] br at absb dot de 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) The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42496 -- Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496 Comment by: martin at catalyst dot net dot nz Reported By: iddekingej at lycos dot com Status: Open Bug Type: OCI8 related Operating System: win 2000 PHP Version: 5.2.4 New Comment: 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. Previous Comments: [2007-11-08 23:38:57] martin at catalyst dot net dot nz 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 $"); [2007-11-08 23:34:29] martin at catalyst dot net dot nz 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! [2007-11-08 16:38:49] br at absb dot de 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) [2007-10-31 17:52:39] natxo dot cabre at gmail dot com 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 ) [2007-10-10 06:14:24] markus dot knecht at psi dot ch Sorry, the build is --with-oci8=instantclient,$ORACLE_HOME The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42496 -- Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496 Comment by: martin at catalyst dot net dot nz Reported By: iddekingej at lycos dot com Status: Open Bug Type: OCI8 related Operating System: win 2000 PHP Version: 5.2.4 New Comment: 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 $"); Previous Comments: [2007-11-08 23:34:29] martin at catalyst dot net dot nz 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! [2007-11-08 16:38:49] br at absb dot de 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) [2007-10-31 17:52:39] natxo dot cabre at gmail dot com 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 ) [2007-10-10 06:14:24] markus dot knecht at psi dot ch Sorry, the build is --with-oci8=instantclient,$ORACLE_HOME [2007-10-09 08:07:43] markus dot knecht at psi dot ch 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. The remainder of the comments for this report are too long. To view the rest of the comments, please view the bug report online at http://bugs.php.net/42496 -- Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496
Comment by: martin at catalyst dot net dot nz
Reported By: iddekingej at lycos dot com
Status: Open
Bug Type: OCI8 related
Operating System: win 2000
PHP Version: 5.2.4
New Comment:
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!
Previous Comments:
[2007-11-08 16:38:49] br at absb dot de
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)
[2007-10-31 17:52:39] natxo dot cabre at gmail dot com
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
)
[2007-10-10 06:14:24] markus dot knecht at psi dot ch
Sorry, the build is --with-oci8=instantclient,$ORACLE_HOME
[2007-10-09 08:07:43] markus dot knecht at psi dot ch
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.
[2007-08-31 21:19:55] iddekingej at lycos dot com
Sorry "$l_cnt=0" is missing from the code. It should read:
$l_res=oci_new_connect("","","");
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);
}
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/42496
--
Edit this bug report at http://bugs.php.net/?id=42496&edit=1
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496
Comment by: br at absb dot de
Reported By: iddekingej at lycos dot com
Status: Open
Bug Type: OCI8 related
Operating System: win 2000
PHP Version: 5.2.4
New Comment:
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)
Previous Comments:
[2007-10-31 17:52:39] natxo dot cabre at gmail dot com
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
)
[2007-10-10 06:14:24] markus dot knecht at psi dot ch
Sorry, the build is --with-oci8=instantclient,$ORACLE_HOME
[2007-10-09 08:07:43] markus dot knecht at psi dot ch
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.
[2007-08-31 21:19:55] iddekingej at lycos dot com
Sorry "$l_cnt=0" is missing from the code. It should read:
$l_res=oci_new_connect("","","");
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);
}
[2007-08-31 15:46:25] iddekingej at lycos dot com
Description:
When a query contains 2 clob fields then the cursor is not closed after
oci_free_statement.
I run the attached script with the following table
tblDocuments
(id number
,v1 clob
,v2 clob
)
After a while the script gives a ora-01000 (maximum number of open
cursors).
When tblDocuments contains only one clob field, the script keeps
running fine.
I can monitor opencursors with the following script:
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current
When I monitor the script and tblDocuments has 2 clobs, the opencursor
increases until maximum number of cursors is reached and the script
failes with a ora -0100.
When I momitor the script and tblDocument has 1 clob, the number of
script stays about the same.
The versions are
Windows 2000 server
Oracle 10.2.0.2 on the same machine
PHP 5.2.4 (File downloaded from php site)
Apache 2.2
Maximum number of cursors is 500
Version 5.2.2 and 5.2.3 has the same problem.
When I enable debug I get the following information
OCI8 DEBUG: OCIHandleAlloc at (ext\oci8\oci8_statement.c:61)
OCI8 DEBUG: OCIStmtPrepare2 at (ext\oci8\oci8_statement.c:77)
OCI8 DEBUG: OCIAttrSet at (ext\oci8\oci8_statement.c:135)
OCI8 DEBUG: OCIAttrSet at (ext\oci8\oci8_statement.c:144)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:418)
OCI8 DEBUG: OCIStmtExecute at (ext\oci8\oci8_statement.c:442)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:471)
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563)
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571)
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:694)
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563)
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571)
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496
Comment by: natxo dot cabre at gmail dot com
Reported By: iddekingej at lycos dot com
Status: Open
Bug Type: OCI8 related
Operating System: win 2000
PHP Version: 5.2.4
New Comment:
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
)
Previous Comments:
[2007-10-10 06:14:24] markus dot knecht at psi dot ch
Sorry, the build is --with-oci8=instantclient,$ORACLE_HOME
[2007-10-09 08:07:43] markus dot knecht at psi dot ch
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.
[2007-08-31 21:19:55] iddekingej at lycos dot com
Sorry "$l_cnt=0" is missing from the code. It should read:
$l_res=oci_new_connect("","","");
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);
}
[2007-08-31 15:46:25] iddekingej at lycos dot com
Description:
When a query contains 2 clob fields then the cursor is not closed after
oci_free_statement.
I run the attached script with the following table
tblDocuments
(id number
,v1 clob
,v2 clob
)
After a while the script gives a ora-01000 (maximum number of open
cursors).
When tblDocuments contains only one clob field, the script keeps
running fine.
I can monitor opencursors with the following script:
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current
When I monitor the script and tblDocuments has 2 clobs, the opencursor
increases until maximum number of cursors is reached and the script
failes with a ora -0100.
When I momitor the script and tblDocument has 1 clob, the number of
script stays about the same.
The versions are
Windows 2000 server
Oracle 10.2.0.2 on the same machine
PHP 5.2.4 (File downloaded from php site)
Apache 2.2
Maximum number of cursors is 500
Version 5.2.2 and 5.2.3 has the same problem.
When I enable debug I get the following information
OCI8 DEBUG: OCIHandleAlloc at (ext\oci8\oci8_statement.c:61)
OCI8 DEBUG: OCIStmtPrepare2 at (ext\oci8\oci8_statement.c:77)
OCI8 DEBUG: OCIAttrSet at (ext\oci8\oci8_statement.c:135)
OCI8 DEBUG: OCIAttrSet at (ext\oci8\oci8_statement.c:144)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:418)
OCI8 DEBUG: OCIStmtExecute at (ext\oci8\oci8_statement.c:442)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:471)
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563)
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571)
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:694)
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563)
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571)
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:676)
OCI8 DEBUG: OCIDefineDynamic at (ext\oci8\oci8_statement.c:719)
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553)
OCI8 DEBUG: OCIAttr
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496
Comment by: markus dot knecht at psi dot ch
Reported By: iddekingej at lycos dot com
Status: Open
Bug Type: OCI8 related
Operating System: win 2000
PHP Version: 5.2.4
New Comment:
Sorry, the build is --with-oci8=instantclient,$ORACLE_HOME
Previous Comments:
[2007-10-09 08:07:43] markus dot knecht at psi dot ch
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.
[2007-08-31 21:19:55] iddekingej at lycos dot com
Sorry "$l_cnt=0" is missing from the code. It should read:
$l_res=oci_new_connect("","","");
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);
}
[2007-08-31 15:46:25] iddekingej at lycos dot com
Description:
When a query contains 2 clob fields then the cursor is not closed after
oci_free_statement.
I run the attached script with the following table
tblDocuments
(id number
,v1 clob
,v2 clob
)
After a while the script gives a ora-01000 (maximum number of open
cursors).
When tblDocuments contains only one clob field, the script keeps
running fine.
I can monitor opencursors with the following script:
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current
When I monitor the script and tblDocuments has 2 clobs, the opencursor
increases until maximum number of cursors is reached and the script
failes with a ora -0100.
When I momitor the script and tblDocument has 1 clob, the number of
script stays about the same.
The versions are
Windows 2000 server
Oracle 10.2.0.2 on the same machine
PHP 5.2.4 (File downloaded from php site)
Apache 2.2
Maximum number of cursors is 500
Version 5.2.2 and 5.2.3 has the same problem.
When I enable debug I get the following information
OCI8 DEBUG: OCIHandleAlloc at (ext\oci8\oci8_statement.c:61)
OCI8 DEBUG: OCIStmtPrepare2 at (ext\oci8\oci8_statement.c:77)
OCI8 DEBUG: OCIAttrSet at (ext\oci8\oci8_statement.c:135)
OCI8 DEBUG: OCIAttrSet at (ext\oci8\oci8_statement.c:144)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:418)
OCI8 DEBUG: OCIStmtExecute at (ext\oci8\oci8_statement.c:442)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:471)
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563)
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571)
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:694)
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563)
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571)
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:676)
OCI8 DEBUG: OCIDefineDynamic at (ext\oci8\oci8_statement.c:719)
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563)
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571)
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:676)
OCI8 DEBUG: OCIDefineDynamic at (ext\oci8\oci8_statement.c:719)
OCI8 DEBUG: OCIStmtFetch at (ext\oci8\oci8_statement.c:168)
OCI8 DEBUG: OCIStmtRelease at (ext\oci8\oci8_statement.c:746)
OCI
#42496 [Com]: cursor is not closed when using 2 clobs in a select query
ID: 42496
Comment by: markus dot knecht at psi dot ch
Reported By: iddekingej at lycos dot com
Status: Open
Bug Type: OCI8 related
Operating System: win 2000
PHP Version: 5.2.4
New Comment:
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.
Previous Comments:
[2007-08-31 21:19:55] iddekingej at lycos dot com
Sorry "$l_cnt=0" is missing from the code. It should read:
$l_res=oci_new_connect("","","");
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);
}
[2007-08-31 15:46:25] iddekingej at lycos dot com
Description:
When a query contains 2 clob fields then the cursor is not closed after
oci_free_statement.
I run the attached script with the following table
tblDocuments
(id number
,v1 clob
,v2 clob
)
After a while the script gives a ora-01000 (maximum number of open
cursors).
When tblDocuments contains only one clob field, the script keeps
running fine.
I can monitor opencursors with the following script:
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursors current
When I monitor the script and tblDocuments has 2 clobs, the opencursor
increases until maximum number of cursors is reached and the script
failes with a ora -0100.
When I momitor the script and tblDocument has 1 clob, the number of
script stays about the same.
The versions are
Windows 2000 server
Oracle 10.2.0.2 on the same machine
PHP 5.2.4 (File downloaded from php site)
Apache 2.2
Maximum number of cursors is 500
Version 5.2.2 and 5.2.3 has the same problem.
When I enable debug I get the following information
OCI8 DEBUG: OCIHandleAlloc at (ext\oci8\oci8_statement.c:61)
OCI8 DEBUG: OCIStmtPrepare2 at (ext\oci8\oci8_statement.c:77)
OCI8 DEBUG: OCIAttrSet at (ext\oci8\oci8_statement.c:135)
OCI8 DEBUG: OCIAttrSet at (ext\oci8\oci8_statement.c:144)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:418)
OCI8 DEBUG: OCIStmtExecute at (ext\oci8\oci8_statement.c:442)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:471)
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563)
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571)
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:694)
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563)
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571)
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:676)
OCI8 DEBUG: OCIDefineDynamic at (ext\oci8\oci8_statement.c:719)
OCI8 DEBUG: OCIParamGet at (ext\oci8\oci8_statement.c:491)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:500)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:510)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:520)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:530)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:543)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:553)
OCI8 DEBUG: OCIAttrGet at (ext\oci8\oci8_statement.c:563)
OCI8 DEBUG: OCIDescriptorFree at (ext\oci8\oci8_statement.c:571)
OCI8 DEBUG: OCIDefineByPos at (ext\oci8\oci8_statement.c:676)
OCI8 DEBUG: OCIDefineDynamic at (ext\oci8\oci8_statement.c:719)
OCI8 DEBUG: OCIStmtFetch at (ext\oci8\oci8_statement.c:168)
OCI8 DEBUG: OCIStmtRelease at (ext\oci8\oci8_statement.c:746)
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8_statement.c:757)
OCI8 DEBUG: OCISessionEnd at (ext\oci8\oci8.c:1523)
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:1527)
OCI8 DEBUG: OCIServerDet
