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