Zend_Db_Statement_Oracle Cursor Results

This one took me a while to figure out, so I thought I would do an entry for it.

These instructions aren’t actually tied to the Zend_Db_Statement_Oracle class in the Zend Framework. I’ve just been using the Zend Framework in the application that this information is from.

Problem

I’ve been using the Oracle database for my application. During development, I found that I needed to return an Oracle cursor to my application from and Oracle function/procedure. By doing so, I could speed up my application and limit the amount of trips to the database. I was trying to accomplish this with the Zend PDO_OCI classes, but found out that cursors really aren’t supported in PDO_OCI. And, from what I read, PDO_OCI isn’t really supported that well either. The Oracle OCI8 drivers are supported more than PDO, but that doesn’t really pertain to this issue.

Solution

After a good amount of research and testing, I found that the following code will call and Oracle function that returns a SYS_REFCURSOR. I had to use the Oracle OCI8 functionality directly here; without going through Zend Framework. The only time I used the framework was to get my database connection information.

...
$sql = "begin :result := myschema.mypackage.myfunction(:evalid , :evaluableid); end;";
$sqlp = oci_parse($tableResponse->getAdapter()->getConnection(), $sql);

$cursor = oci_new_cursor($tableResponse->getAdapter()->getConnection());
oci_bind_by_name($sqlp,':evalid', $post['evalid'],20, SQLT_CHR);
oci_bind_by_name($sqlp,':evaluableid',$user['EVALUABLEID'],20, SQLT_CHR);
oci_bind_by_name($sqlp,':result', $cursor,-1,SQLT_RSET);
$didExecute = oci_execute($sqlp, $tableResponse->getAdapter()->_getExecuteMode());
$cursorDidExecute = oci_execute($cursor);
			
$result = array();
$data = oci_fetch_all($cursor, $result);
var_dump($result);
...

I’m going to try installing this functionality in Zend, but it will have to be done via a patch to the /Zend/Db/Statement/Oracle class. I found a patch on the following website: http://framework.zend.com/wiki/display/ZFPROP/Zend_Db_Cursor+-+Bruno+Piraja+Moyle

If that link is ever broken, this was the code listed:

### Eclipse Workspace Patch 1.0
#P Zend Framework
Index: library/Zend/Db/Statement/Oracle.php
===================================================================
--- library/Zend/Db/Statement/Oracle.php    (revision 22149)
+++ library/Zend/Db/Statement/Oracle.php    (working copy)
@@ -115,6 +115,19 @@
$type = SQLT_CHR;}
  
+        if($type == Zend_Db::PARAM_STMT){
+            $type = OCI_B_CURSOR;
+            $variable = @oci_new_cursor($this->_adapter->getConnection());
+            if (empty($variable)) {
+                /**
+                 * @see Zend_Db_Adapter_Oracle_Exception
+                 */
+                require_once 'Zend/Db/Statement/Oracle/Exception.php';
+                $error = array("code" => "", "message" => "Error to create oracle cursor");
+                throw new Zend_Db_Statement_Oracle_Exception(oci_error($variable));
+            }
+        }
+
         // default value
         if ($length === NULL) {
             $length = -1;
@@ -276,6 +289,23 @@
             $this->_values = array_fill(0, count($this->_keys), null);
         }
  
+        foreach($this->_bindParam as &$value){
+            if(is_resource($value)
+            && get_resource_type($value) == "oci8 statement"){
+                $retvalCursor = @oci_execute($value, $this->_adapter->_getExecuteMode());
+                if ($retvalCursor === false) {
+                    /**
+                     * @see Zend_Db_Adapter_Oracle_Exception
+                     */
+                    require_once 'Zend/Db/Statement/Oracle/Exception.php';
+                    throw new Zend_Db_Statement_Oracle_Exception(oci_error($value));
+                }
+                $stmt = $value;
+                $value = clone $this;
+                $value->_stmt = $stmt;
+            }
+        }
+
         return $retval;
     }