|  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #75563 Setting oci8.statement_cache_size >= open_cursors leads to a sure ORA-1000
Submitted: 2017-11-23 16:50 UTC Modified: 2017-11-23 22:37 UTC
From: dark dot epistemology at gmail dot com Assigned: tianfyan (profile)
Status: Feedback Package: OCI8 related
PHP Version: 7.1.11 OS: Linux 2.6.32-642.6.2.el6.x86_64
Private report: No CVE-ID: None
Have you experienced this issue?
Rate the importance of this bug to you:

 [2017-11-23 16:50 UTC] dark dot epistemology at gmail dot com
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
// Create connection to Oracle
$cx = oci_connect("/", "", "TST", null, OCI_CRED_EXT );
if (!$cx) {
   $m = oci_error();
   echo $m['message'], "\n";

$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

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


Add a Patch

Pull Requests

Add a Pull Request


AllCommentsChangesGit/SVN commitsRelated reports
 [2017-11-23 22:37 UTC]
-Status: Open +Status: Feedback
 [2017-11-23 22:37 UTC]
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.
 [2017-11-23 22:37 UTC]
-Assigned To: +Assigned To: tianfyan
PHP Copyright © 2001-2018 The PHP Group
All rights reserved.
Last updated: Sun Nov 19 01:31:42 2017 UTC