Issues

ZF-5758: Oracle error : '1000 ORA-01000: maximum open cursors exceeded *SELECT S_CTO_PAC.NEXTVAL FROM dual'

Description

Hi,

I use thz ZF for a couple og years now but only for some packages. I want to use it dephly (and MVC + Zend_Db).

I got an issue with the Zend_Db_Table as soon as I want to execute more SQL than I/the DBA have/has authorized cursors... even if they should be closed by the ZF...

Here is my example:


CREATE TABLE "T_CTO_PAC2" 
(
    "ID_PAC" NUMBER(38,0), 
    "PAC" CHAR(6 BYTE), 
    "CID" VARCHAR2(64 BYTE), 
    "LABEL" VARCHAR2(128 BYTE), 
    "CREATED_ON" DATE DEFAULT sysdate, 
    "UPDATED_ON" DATE, 
    CONSTRAINT "CK_CTO_PAC2_CID" CHECK (CID is not null) ENABLE, 
    CONSTRAINT "CK_CTO_PAC2_LABEL" CHECK (LABEL is not null) ENABLE, 
    CONSTRAINT "PK_CTO_PAC2" PRIMARY KEY ("ID_PAC") USING INDEX TABLESPACE "CTO_INDEX"  ENABLE, 
    CONSTRAINT "U_CTO_PAC_PAC2" UNIQUE ("PAC")  USING INDEX TABLESPACE "CTO_INDEX"  ENABLE
) TABLESPACE "CTO_DATA";

class TPac extends Cto_Core_Db_Table_Abstract {

    /** @var string Table name */
    protected $_name = 'T_CTO_PAC2';
    /** @var string PK */
    protected $_primary = 'ID_PAC';
    /** @var string Sequence name */
    protected $_sequence = 'S_CTO_PAC';

}

$pac = new TPac();
//$pac->getAdapter()->beginTransaction();
for ($i = 100; $i < 1901; ++$i) {
    $p = str_pad($i, 6, '0', STR_PAD_LEFT);
    
//--- This hard coded SQL string works....
//            $sql = "insert into T_CTO_PAC2
//                    cols (ID_PAC, PAC, LABEL, CID)
//                    values (S_CTO_PAC.nextval, '$p', 'label $i', 'TEST WILL')";
//
//            $pac->getAdapter()->query($sql);
    
    //--- This insert through the Adapter DOESN'T work
    $pac->getAdapter()->insert('T_CTO_PAC2',
                               array(
                                   'ID_PAC' => new Zend_Db_Expr('S_CTO_PAC.nextval'),
                                   'PAC' => str_pad($i, 6, '0', STR_PAD_LEFT),
                                   'LABEL' => "label $i",
                                   'CID' => 'TEST WILL'
                               )
                              );
                              
//--- This insert through the Adapter DOESN'T work either
//            $pac->insert(array('PAC' => str_pad($i, 6, '0', STR_PAD_LEFT),
//                               'LABEL' => "label $i",
//                               'CID' => 'TEST WILL'));
}

//$pac->getAdapter()->commit();
return;

That works for $i < 200, but with $i < 1901, it breaks:

Fatal error: Uncaught exception 'Zend_Db_Statement_Oracle_Exception' with message '1000 ORA-01000: maximum open cursors exceeded
    *INSERT INTO T_CTO_PAC2 (ID_PAC, PAC, LABEL, CID) VALUES (S_CTO_PAC.nextval, :PAC1, :LABEL2, :CID3)'
    in /ehc/fs1/softs/spms/php_lib/ZendFramework-1.7.4/library/Zend/Db/Statement/Oracle.php:274 Stack trace: 
#0 /ehc/fs1/softs/spms/php_lib/ZendFramework-1.7.4/library/Zend/Db/Statement.php(303): Zend_Db_Statement_Oracle->_execute(Array) 
#1 /ehc/fs1/softs/spms/php_lib/ZendFramework-1.7.4/library/Zend/Db/Adapter/Abstract.php(433): Zend_Db_Statement->execute(Array) 
#2 /ehc/fs1/softs/spms/php_lib/ZendFramework-1.7.4/library/Zend/Db/Adapter/Oracle.php(641): Zend_Db_Adapter_Abstract->query('INSERT INTO T_C...', Array) 
#3 /ehc/fs1/adp/spms/web-app/spms.wloche/application/default/controllers/IndexController.php(57): Zend_Db_Adapter_Oracle->insert('T_CTO_PAC2', Array) 
#4 /ehc/fs1/softs/spms/php_lib/ZendFramework-1.7.4/library/Zend/Controller/Action.php(503): IndexController->indexAction()
#5 /ehc/fs1/softs/s in /ehc/fs1/softs/spms/php_lib/ZendFramework-1.7.4/library/Zend/Db/Statement/Oracle.php on line 274

Does anybody already got this issue? We got some workarroud and optimization we will propose in a next post.

Thx, Wilfried Loche

PS. Env : - PHP 5.2.8, - Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options - tnsname.ora: SPMS11=(DESCRIPTION=(SOURCE_ROUTE=OFF)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=11020)))(CONNECT_DATA=(SID=SPMS11)(SERVER=))) - using DRCP

Comments

Am I the only one who use Oracle? :D

No, we use Oracle extensively, we just haven't had this issue.

Have you tried:


$pacModel = new TPac();

for ($i = 100; $i < 1901; ++$i) {
    $pac = $pacModel->createRow();
    $pac->PAC = str_pad($i, 6, '0', STR_PAD_LEFT);
    $pac->LABEL = "label $i";
    $pac->CID = 'TEST WILL';
    $pac->save();
}

return;

Hi Roger,

Happy to have some good news (I didn't see you answer :(). I'm afraid I have exactly the same issue...

Fatal error:  Uncaught exception 'Zend_Db_Statement_Oracle_Exception' with message '604 ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded SELECT z2.*
            FROM (
                SELECT z1.*, ROWNUM AS "zend_db_rownum"
                FROM (
                    SELECT T_CTO_PAC2.* FROM *T_CTO_PAC2 WHERE (T_CTO_PAC2.ID_PAC = 7037.000000)
                ) z1
            ) z2
            WHERE z2."zend_db_rownum" BETWEEN 1 AND 1' in /ehc/fs1/softs/spms/php_lib/ZendFramework-1.9.1/library/Zend/Db/Statement/Oracle.php:275
Stack trace:
#0 /ehc/fs1/softs/spms/php_lib/ZendFramework-1.9.1/library/Zend/Db/Statement.php(304): Zend_Db_Statement_Oracle->_execute(Array)
#1 /ehc/fs1/softs/spms/php_lib/ZendFramework-1.9.1/library/Zend/Db/Adapter/Abstract.php(468): Zend_Db_Statement->execute(Array)
#2 /ehc/fs1/adp/spms/web-app/spms.wloche/library/Cto/Core/Db/Adapter/Oracle.php(120): Zend_Db_Adapter_Abstract->query('SELECT z2.*?   ...', Array)
#3 /ehc/fs1/softs/spms/php_lib/ZendFramework-1.9. in /ehc/fs1/softs/spms/php_lib/ZendFramework-1.9.1/library/Zend/Db/Statement/Oracle.php on line 275

Well... this is not he same ORA error. Seems there is a clue there: [http://dba-oracle.com/sf_ora_00604_error_occurred_…]

Our dba will look at it.

Thx, Wilfried

Oh man, you should use you DBMS the rights way :


$sqlQuery = 'insert into T1 (C1, C2, C3) values (:c1, :c2, :c3)';
$statement = new Zend_Db_Statement_Oracle($dbAdapter, $sqlQuery);
for ($i = 100; $i < 1901; ++$i) {
    $statement->execute(array(
        ':c1' => 'Test C1',
        ':c2' => 'Test C2',
        ':c3' => 'Test C3',
    ));
}

It's the only way to Oracle doing noparse for the query and to have the best speed. (or you could have done it with a PL/SQL stored procedures + a VARRAY as parameter).