Re: [fw-db] Oracle cursors returned from stored procedures

Hey,
first i wanna say that it was a fast solution but working for me very well,
nonetheless it can be more flexible, because now this method binds always name *cursor* to the statement.

Usage:
        $params['rowsPerPage'] = 1;
        $params['order'] = 'date_added';
        $params['order_dir'] = 'ASC';
        $params['adId'] = (int)$adId;
        $sql = 'BEGIN dik.get_adverts(:cursor, :rowsPerPage, :order, :order_dir, :adId); END;';
       
        $rows = $this->_db->fetchCursor($sql, $params);

Code:

class My_Db_Adapter_Oracle extends Zend_Db_Adapter_Oracle
{
    public function fetchCursor($sql, $bind = array())
    {       
        $data = array();
        $conn = $this->getConnection();
       
        $curs = oci_new_cursor($conn);
        $stmt = oci_parse($conn, $sql);
       
        oci_bind_by_name($stmt, "cursor", $curs, -1, OCI_B_CURSOR);
        foreach ($bind as $key=>&$val) {
            oci_bind_by_name($stmt, $key, $val, -1, OCI_ASSOC);
        }

        oci_execute($stmt);
        if ($e = oci_error($stmt)) {
            throw new Zend_Db_Adapter_Oracle_Exception($e, -1234);
        }
        oci_execute($curs);
       
        if (oci_fetch_all($curs, $data, 0, -1, OCI_FETCHSTATEMENT_BY_ROW)) {
          //  var_dump($data);
        }
       
        oci_free_statement($stmt);
        oci_free_statement($curs);
       
        return $data;
    }
}

--
Pagarbiai,
Vladas Diržys
Tel.: +370 620 69020
www.dirzys.com

On Thu, Jul 31, 2008 at 5:45 PM, tr0gd0rr <kendsnyder@gmail.com> wrote:

Gabriel Baez-2 wrote:
>
> This is how I call a oracle procedure using PDO

> ...
>

@Gabriel

Thanks for the input!

@funkyfly

I'd be so thankful if you could post some of your code!

Thanks,

Ken Snyder


--
View this message in context: http://www.nabble.com/Oracle-cursors-returned-from-stored-procedures-tp18634078p18756215.html

Sent from the Zend DB mailing list archive at Nabble.com.