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("<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);
}
------------------------------------------------------------------------
[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: OCIServerDetach at (ext\oci8\oci8.c:1531)
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:1535)
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:1539)
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:1543)
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:1547)
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:461)
OCI8 DEBUG: OCIHandleFree at (ext\oci8\oci8.c:466)
Reproduce code:
---------------
$l_res=oci_new_connect("<username>","<password>","<sid>");
oci_execute($l_st);
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);
}
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=42496&edit=1
