Bug #75563 [Opn->Fbk]: Setting oci8.statement_cache_size >= open_cursors leads to a sure ORA-1000
Edit report at https://bugs.php.net/bug.php?id=75563&edit=1
ID: 75563
Updated by: sixd@php.net
Reported by: dark dot epistemology at gmail dot com
Summary: Setting oci8.statement_cache_size >= open_cursors
leads to a sure ORA-1000
-Status: Open
+Status: Feedback
Type: Bug
Package: OCI8 related
Operating System: Linux 2.6.32-642.6.2.el6.x86_64
PHP Version: 7.1.11
Block user comment: N
Private report: N
New Comment:
Somethings going on. Maybe recursive SQL?
I couldn't reproduce the problem - I tried several DB versions, different loop iterations and
open_cursor values.
Also, doing any DB query per connection just to get the settings (even if the user has access) will
kill performance, so I think this is best left to the developer to tune.
Previous Comments:
------------------------------------------------------------------------
[2017-11-23 16:50:41] dark dot epistemology at gmail dot com
Description:
------------
Set oci8.statement_cache_size = 30 in php.ini
Set open_cursors = 30 in Oracle.
Run the script and die on the 31st cursor:
Cursor 30 executed
Warning: oci_execute(): ORA-01000: maximum open cursors exceeded in /home/dke/work/cursors/php/t2 on
line 17
Test script:
---------------
#!/bin/env php
<?php
// Create connection to Oracle
$cx = oci_connect("/", "", "TST", null, OCI_CRED_EXT );
if (!$cx) {
$m = oci_error();
echo $m['message'], "\n";
exit;
}
$st = oci_parse($cx, "ALTER SESSION SET tracefile_identifier='T2' SQL_TRACE=
TRUE");
oci_execute( $st );
$qry = "select 1 + %d from dual";
for ($i=1; $i<=31; $i++) {
$st = oci_parse($cx, sprintf( $qry, $i) );
oci_execute($st, OCI_DEFAULT);
oci_fetch_all($st, $res);
oci_free_statement( $st );
printf( "Cursor %d executed\n", $i );
}
// Close the Oracle connection
oci_close($cx);
?>
Expected result:
----------------
I expect not to die on ORA-1000. I'm properly closing my cursors, after all.
There should be a sanity check on oci8.statement_cache_size that ensures it is strictly inferior to
the Oracle parameter open_cursors.
Actual result:
--------------
Cursor 30 executed
Warning: oci_execute(): ORA-01000: maximum open cursors exceeded in /home/dke/work/cursors/php/t2 on
line 17
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=75563&edit=1
Thread (3 messages)