php.net |  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
View Add Comment Developer Edit
Welcome! If you don't have a Git account, you can't do anything here.
You can add a comment by following this link or if you reported this bug, you can edit this bug over here.
(description)
Block user comment
Status: Assign to:
Package:
Bug Type:
Summary:
From: dark dot epistemology at gmail dot com
New email:
PHP Version: OS:

 

 [2017-11-23 16:50 UTC] 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

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

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