[fw-db] Zend_Db and Oracle Cursors from Stored Procedures

We are using Oracle and ran into a situation where we need to access cursors
as output paramters of a stored procedure and realized that the
functionality doesn't currently exist in any Zend_Db classes. A cursor can
be obtained from the oci_new_cursor() function, however the
Zend_Db_Statement_Oracle class doesn't provide access to create the cursor
and bind it to the statement.

To remedy this situation, we have created a new class, Zend_Db_Cursor_Oracle
which extends Zend_Db_Statement_Oracle and can be bound as a parameter to a
statement and iterated through as a normal resultset. The
Zend_Db_Adapter_Oracle class was modifed to return an instance of
Zend_Db_Cursor_Oracle.

Sample usage follows:
$values = array(
':P_CUSTOMER_ID' => $customerSearchRequest->customerId,
':P_FIRST_NAME' => $customerSearchRequest->firstName,
':P_LAST_NAME' => $customerSearchRequest->lastName,
);

$sql = "CALL SEARCH_CUSTOMERS(" . implode(array_keys($values), ",") .
",:P_CUSTOMERLIST)";

//$config is an instance of Zend_Config pointing to an XML file
$adapter = Zend_Db::factory($config);

//Get a cursor statement from the Adapter
$cursor = $adapter->newCursor();
$oraCursor = $cursor->getCursor();

//Prepare the Adapter with the SQL
$stmt = $adapter->prepare($sql);
//Bind the return value of the Stored Proc. to the cursor
$stmt->bindParam("P_CUSTOMERLIST", $oraCursor, OCI_B_CURSOR, -1);

//Execute the Statement (with values) and the Cursor
$stmt->execute($values);
$cursor->execute();

$results = array();
while (false !== ($row = $cursor->fetch(Zend_Db::FETCH_ASSOC))) {
$results[] = $row;
}

return $results;

Would anyone else find this functionality useful if added to the Zend
Framework? Does anyone know of any other DBMSs that use ref cursors in the
same manner as Oracle? I couldn't find anything online but SQL Server may
work in the same way.

reference:
http://www.oracle.com/technology/pub/articles/oracle_php_cookbook/fuecks_sps.html

Thanks,
-Tom Allen
--
View this message in context: http://www.nabble.com/Zend_Db-and-Oracle-Cursors-from-Stored-Procedures-tp19444809p19444809.html
Sent from the Zend DB mailing list archive at Nabble.com.