Yii handling “getLastInsertId” with Oracle

With MySQL or SQLite, when you insert something with an auto_increment field, it will automatically deal with the ActiveRecord by putting the last inserted id into $model->id. Because Oracle needs sequences and triggers to deal with that, neither the PDO driver nor the Yii PDO code felt it necessary to deal with that. Most people are probably fine with commiting to a database and throwing in Oracle specific sequence_name.nextval.

I just added a class in the models to extend CActiveRecord. The only thing you need to do with this is have your model extend this instead of CActiveRecord and add the variable schemaName to the model:

class User extends QActiveRecord
	public $sequenceName = 'USERS_SEQ';	


  1. Waqar says:

    The generic solution for this problem will be:

    public function afterSave()
    $pk = $this->tableSchema->primaryKey;
    if(Yii::app()->db->driverName == ‘oci’)
    $connection = Yii::app()->db;
    $sql = “SELECT {$this->sequence}.CURRVAL FROM DUAL”;
    $result = $connection->createCommand($sql)->queryRow();
    $this->$pk = $result[‘CURRVAL’];
    catch (Exception $e)
    echo(“Error: $e\n”);
    return $this->$pk;

  2. Roberto says:

    Getting the {$this->sequence}.CURRVAL is not safe because, even if the possibility is very small, it can give a wrong value in case in the meanwhile another user made an insert.
    It would be better if you you use beforesave() to retrieve the sequence NEXTVAL and assign it to the id filed of the model.
    In this way you surely keep the consistence of the key.

Leave a comment