We started using ZF at version 1.5 or so, and at that time it didn't seem to support BLOBs - I haven't looked recently to see if this has changed.

We have a My_Db_Adapter_Oracle which overrides Zend_Db_Adapter_Oracle and overrides the insert() function as follows (there is some extra stuff to track whether a transaction is already open):

    /**
     * @var boolean
     */
    protected $_transactionOpen = null;

    /**
     * Identifies whether the adapter has an open transaction
     *
     * @return boolean
     */
    protected function _hasOpenTransaction()
    {
        return $this->_transactionOpen;
    }

    /**
     * Leave autocommit mode and begin a transaction.
     *
     * Overloads Zend_Db_Adapter_Oracle::_beginTransaction to
     * track the open transaction
     *  
     * @return void
     */
    protected function _beginTransaction()
    {
        // Do the parent code
        parent::_beginTransaction();

        $this->_transactionOpen = true;       
    }

    /**
     * Commit a transaction and return to autocommit mode.
     *
     * Overloads Zend_Db_Adapter_Oracle::_commit to track
     * the open transaction
     * 
     * @return void
     * @throws Zend_Db_Adapter_Oracle_Exception
     */
    protected function _commit()
    {
        $this->_transactionOpen = false;

        // Do the parent code
        parent::_commit();        
    }

    /**
     * Roll back a transaction and return to autocommit mode.
     *
     * Overloads Zend_Db_Adapter_Oracle::_rollBack to track
     * the open transaction
     * 
     * @return void
     * @throws Zend_Db_Adapter_Oracle_Exception
     */
    protected function _rollBack()
    {
        $this->_transactionOpen = false;
        
        // Do the parent code
        parent::_rollBack();
    }

    /**
     * Inserts a table row with specified data.
     *
     * Overloads Zend_Db_Adapter_Oracle to support BLOB columns
     *
     * Oracle does not support anonymous ('?') binds.
     *
     * @param mixed $table The table to insert data into.
     * @param array $bind Column-value pairs.
     * @return int The number of affected rows.
     */
    public function insert($table, array $bind)
    {
        // Use transaction management rather than commit on success
        $transactionOpenedHere = false;
        if (!$this->_hasOpenTransaction()) {
            $this->beginTransaction();
            $transactionOpenedHere = true;
        }

        // Get the table metadata
        $columns = $this->describeTable($table);
        
        // Check the columns in the array against the database table
        // to identify BLOB (or CLOB) columns
        foreach (array_keys($bind) as $column) {
            if ( in_array($columns[$column]['DATA_TYPE'], array('BLOB', 'CLOB'))) {
                $lobs[]=$column;
            }
        }

        // If there are no blob columns then use the normal insert procedure
        if ( !isset($lobs)) {
            $result = parent::insert($table, $bind);

        } else {            
            // There are blobs in the $bind array so insert them separately
            $ociTypes = array('BLOB' => OCI_B_BLOB, 'CLOB' => OCI_B_CLOB);

            // Extract and quote col names from the array keys
            $i = 0;
            $cols = array();
            $vals = array();
            foreach ($bind as $col => $val) {
                $cols[] = $this->quoteIdentifier($col, true);
                if (in_array($col, $lobs)) {
                    $vals[] = 'EMPTY_' . $columns[$col]['DATA_TYPE'] . '()';
                    $lobData[':'.$col.$i] = array('ociType' => $ociTypes[$columns[$col]['DATA_TYPE']],
                                                  'data'    => $val);
                    unset($bind[$col]);
                    $lobDescriptors[':'.$col.$i] = oci_new_descriptor($this->_connection, OCI_D_LOB);
                    $returning[] = ':'.$col.$i;
                    $bind[':'.$col.$i] = $lobDescriptors[':'.$col.$i];
                } elseif ($val instanceof Zend_Db_Expr) {
                    $vals[] = $val->__toString();
                    unset($bind[$col]);
                } else {
                    $vals[] = ':'.$col.$i;
                    unset($bind[$col]);
                    $bind[':'.$col.$i] = $val;
                }
                $i++;
            }
            
            // build the statement
            $sql = "INSERT INTO "
                 . $this->quoteIdentifier($table, true)
                 . ' (' . implode(', ', $cols) . ') '
                 . 'VALUES (' . implode(', ', $vals) . ') '
                 . 'RETURNING ' . implode(', ', $lobs) . ' '
                 . 'INTO '  . implode(', ', $returning);

            // Execute the statement
            $stmt = new Zend_Db_Statement_Oracle($this, $sql);
            foreach (array_keys($bind) as $name) {
                if (in_array($name, array_keys($lobData))) {
                    $stmt->bindParam($name, $bind[$name], $lobData[$name]['ociType'], -1);
                } else {
                    $stmt->bindParam($name, $bind[$name]);
                }
            }

            //Execute without committing
            $stmt->execute();
            $result = $stmt->rowCount();
            
            // Write the LOB data & free the descriptor
            foreach ( $lobDescriptors as $name => $lobDescriptor) {            
                $lobDescriptor->write($lobData[$name]['data']);
                $lobDescriptor->free();
            }
            
        }

        // Commit
        if ($transactionOpenedHere) {
            $this->commit();
        }
        
        // Return result
        return $result;

    }

It shouldn't be too hard for you to take this approach with your application.

It should also be possible to add this code into Zend_Db_Adapter_Oracle if any of the maintainers are interested.

I would be happy to help with it if one of the maintainers can tell me what needs to be done.

We use zend framework at work with a Oracle Database this feature would definitely make things easier to deal with BLOBs.

I would like to give my contribution. Advantage the insert function, I changed it to recognize the situation where hava SCHEMA and TABLE in $table var, and we can use this idea to the update option: \ \

/**
     * Inserts a table row with specified data.
     *
     * Overloads Zend_Db_Adapter_Oracle to support BLOB columns
     *
     * Oracle does not support anonymous ('?') binds.
     * Alteração para funcionar com campos BLOB e CLOB
     * @param mixed $table The table to insert data into.
     * @param array $bind Column-value pairs.
     * @return int The number of affected rows.
     */
    public function insert($table, array $bind)
    {
        // Use transaction management rather than commit on success
        $transactionOpenedHere = false;
        if (!$this->_hasOpenTransaction()) {
            $this->beginTransaction();
            $transactionOpenedHere = true;
        }
        $tb = explode(".", $table);
        if(count($tb) == 2)
        {
            $tbName = $tb[1];
            $tbSchema = $tb[0];
        }else
            $tbName = $tb[0];
        // Get the table metadata
        $columns = $this->describeTable($tbName, @$tbSchema);

        // Check the columns in the array against the database table
        // to identify BLOB (or CLOB) columns
        foreach (array_keys($bind) as $column) {
            if ( in_array($columns[$column]['DATA_TYPE'], array('BLOB', 'CLOB'))) {
                $lobs[]=$column;
            }
        }

        // If there are no blob columns then use the normal insert procedure
        if ( !isset($lobs)) {
            $result = parent::insert($table, $bind);

        } else {
            // There are blobs in the $bind array so insert them separately
            $ociTypes = array('BLOB' => OCI_B_BLOB, 'CLOB' => OCI_B_CLOB);

            // Extract and quote col names from the array keys
            $i = 0;
            $cols = array();
            $vals = array();
            foreach ($bind as $col => $val) {
                $cols[] = $this->quoteIdentifier($col, true);
                if (in_array($col, $lobs)) {
                    $vals[] = 'EMPTY_' . $columns[$col]['DATA_TYPE'] . '()';
                    $lobData[':'.$col.$i] = array('ociType' => $ociTypes[$columns[$col]['DATA_TYPE']],
                                                  'data'    => $val);
                    unset($bind[$col]);
                    $lobDescriptors[':'.$col.$i] = oci_new_descriptor($this->_connection, OCI_D_LOB);
                    $returning[] = ':'.$col.$i;
                    $bind[':'.$col.$i] = $lobDescriptors[':'.$col.$i];
                } elseif ($val instanceof Zend_Db_Expr) {
                    $vals[] = $val->__toString();
                    unset($bind[$col]);
                } else {
                    $vals[] = ':'.$col.$i;
                    unset($bind[$col]);
                    $bind[':'.$col.$i] = $val;
                }
                $i++;
            }

            // build the statement
            $sql = "INSERT INTO "
                 . $this->quoteIdentifier($table, true)
                 . ' (' . implode(', ', $cols) . ') '
                 . 'VALUES (' . implode(', ', $vals) . ') '
                 . 'RETURNING ' . implode(', ', $lobs) . ' '
                 . 'INTO '  . implode(', ', $returning);

            // Execute the statement
            $stmt = new Zend_Db_Statement_Oracle($this, $sql);
            foreach (array_keys($bind) as $name) {
                if (in_array($name, array_keys($lobData))) {
                    $stmt->bindParam($name, $bind[$name], $lobData[$name]['ociType'], -1);
                } else {
                    $stmt->bindParam($name, $bind[$name]);
                }
            }

            //Execute without committing
            $stmt->execute();
            $result = $stmt->rowCount();

            // Write the LOB data & free the descriptor
            foreach ( $lobDescriptors as $name => $lobDescriptor) {
                $lobDescriptor->write($lobData[$name]['data']);
                $lobDescriptor->free();
            }

        }

        // Commit
        if ($transactionOpenedHere) {
            $this->commit();
        }

        // Return result
        return $result;

    }
    /**
     * 
     * Updates table rows with specified data based on a WHERE clause.
     *
     * @param  mixed        $table The table to update.
     * @param  array        $bind  Column-value pairs.
     * @param  mixed        $where UPDATE WHERE clause(s).
     * @return int          The number of affected rows.
     */
    public function update($table, array $bind, $where = '')
    {
       // Use transaction management rather than commit on success
        $transactionOpenedHere = false;
        if (!$this->_hasOpenTransaction()) {
            $this->beginTransaction();
            $transactionOpenedHere = true;
        }
        $tb = explode(".", $table);
        if(count($tb) == 2)
        {
            $tbName = $tb[1];
            $tbSchema = $tb[0];
        }else
            $tbName = $tb[0];
        // Get the table metadata
        $columns = $this->describeTable($tbName, @$tbSchema);

        // Check the columns in the array against the database table
        // to identify BLOB (or CLOB) columns
        foreach (array_keys($bind) as $column) {
            if ( in_array($columns[$column]['DATA_TYPE'], array('BLOB', 'CLOB'))) {
                $lobs[]=$column;
            }
        }

        // If there are no blob columns then use the normal insert procedure
        if ( !isset($lobs)) {
            $result = parent::update($table, $bind, $where);

        } else {
            // There are blobs in the $bind array so insert them separately
            $ociTypes = array('BLOB' => OCI_B_BLOB, 'CLOB' => OCI_B_CLOB);

            /**
             * Build "col = ?" pairs for the statement,
             * except for Zend_Db_Expr which is treated literally.
             */
            $set = array();
            $i = 0;
            foreach ($bind as $col => $val) {
                if (in_array($col, $lobs))
                {
                    $lobData[':'.$col.$i] = array('ociType' => $ociTypes[$columns[$col]['DATA_TYPE']],
                                                  'data'    => $val);
                    unset($bind[$col]);
                    $lobDescriptors[':'.$col.$i] = oci_new_descriptor($this->_connection, OCI_D_LOB);
                    $returning[] = ':'.$col.$i;
                    $bind[':'.$col.$i] = $lobDescriptors[':'.$col.$i];
                    $val = 'EMPTY_' . $columns[$col]['DATA_TYPE'] . '()';
                }elseif ($val instanceof Zend_Db_Expr) {
                    $val = $val->__toString();
                    unset($bind[$col]);
                } else {
                    if ($this->supportsParameters('positional')) {
                        $val = '?';
                    } else {
                        if ($this->supportsParameters('named')) {
                            unset($bind[$col]);
                            $bind[':'.$col.$i] = $val;
                            $val = ':'.$col.$i;
                            $i++;
                        } else {
                            /** @see Zend_Db_Adapter_Exception */
                            require_once 'Zend/Db/Adapter/Exception.php';
                            throw new Zend_Db_Adapter_Exception(get_class($this) ." doesn't support positional or named binding");
                        }
                    }
                }
                $set[] = $this->quoteIdentifier($col, true) . ' = ' . $val;
            }

            $where = $this->_whereExpr($where);

            /**
             * Build the UPDATE statement
             */
            $sql = "UPDATE "
                 . $this->quoteIdentifier($table, true)
                 . ' SET ' . implode(', ', $set)
                 . (($where) ? " WHERE $where" : ''). ' '
                 . 'RETURNING ' . implode(', ', $lobs) . ' '
                 . 'INTO '  . implode(', ', $returning);

            // Execute the statement
            $stmt = new Zend_Db_Statement_Oracle($this, $sql);
            foreach (array_keys($bind) as $name) {
                if (in_array($name, array_keys($lobData))) {
                    $stmt->bindParam($name, $bind[$name], $lobData[$name]['ociType'], -1);
                } else {
                    $stmt->bindParam($name, $bind[$name]);
                }
            }

            //Execute without committing
            $stmt->execute();
            $result = $stmt->rowCount();

            // Write the LOB data & free the descriptor
            foreach ( $lobDescriptors as $name => $lobDescriptor) {
                $lobDescriptor->write($lobData[$name]['data']);
                $lobDescriptor->free();
            }

        }

        // Commit
        if ($transactionOpenedHere) {
            $this->commit();
        }

        // Return result
        return $result;
    }

I don't like the solution using describeTable(), this seems like a serious performance hit for users that don't realize that insert() and update() need this function and therefore a cache should be wrapped around it.

Since you retrieve a OCI-LOB instance anways from a select on a BLOB or CLOB field, wouldn'it be much more easy just to allow this methods to handle instances of that type correctly?

For example:

$lob = oci_new_descriptor($db->getConnection(), OCI_DTYPE_LOB);
$lob->write($text);
$data = array(
    'name' => $name,
    'text' => $lob,
);
$db->insert('table', $data);

I just realized my solution isnt enough, the information about CLOB or BLOB is missing for the insert function, forget it :-)

Any updates on this issue?

A litle solution for Zend models. See.

Model Cars

class Custom_Row_Cars extends Zend_Db_Table_Row_Abstract {

public function init() { 

    foreach($this->_data as $key => $value ) { 
        if( is_resource($value) ) { 
           $this->_data[$key] = stream_get_contents($value);
        }
    }

} 

}

class Application_Model_DbTable_Cars extends Zend_Db_Table_Abstract {

protected $_name = 'CARS';
protected $_primary = 'ID';
protected $_rowClass = 'Custom_Row_Cars'; // Custom Row for data manipulation

public function getById($id) {
    return $this->fetchRow($this->select()->where('ID = ?', $id));
}

}

CLOB field are resource. In case are read with function stream_get_contents