ORA-00936: missing expression error

Yii 1.1 Installation and Setup
1

Hi bros,

I have used gii to setup the crud for my oracle tables, but encounter below error when doing ‘UPDATE’ or ‘DELETE’ operations. ‘CREATE’ and ‘VIEW’ are working fine. I’m using oci connection.

Description

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 936 OCIStmtExecute: ORA-00936: missing expression

(/tmp/PDO_OCI-1.0/oci_statement.c:142)

Source File

/var/www/yii/framework/db/CDbCommand.php(234)

00222: if($this->_connection->enableProfiling)

00223: Yii::endProfile(‘system.db.CDbCommand.execute(’.$this->getText().’)’,‘system.db.CDbCommand.execute’);

00224:

00225: return $n;

00226: }

00227: catch(Exception $e)

00228: {

00229: if($this->_connection->enableProfiling)

00230: Yii::endProfile(‘system.db.CDbCommand.execute(’.$this->getText().’)’,‘system.db.CDbCommand.execute’);

00231: Yii::log('Error in executing SQL: '.$this->getText().$par,CLogger::LEVEL_ERROR,‘system.db.CDbCommand’);

00232: $errorInfo = $e instanceof PDOException ? $e->errorInfo : null;

00233:

00234: throw new CDbException(Yii::t(‘yii’,‘CDbCommand failed to execute the SQL statement: {error}’,

00235: array(’{error}’=>$e->getMessage())),(int)$e->getCode(),$errorInfo);

00236: }

00237: }

Stack Trace

#0 /var/www/yii/framework/db/ar/CActiveRecord.php(1591): CDbCommand->execute()

#1 /var/www/yii/framework/db/ar/CActiveRecord.php(1047): CActiveRecord->updateByPk(‘22’, Array)

#2 /var/www/yii/framework/db/ar/CActiveRecord.php(759): CActiveRecord->update(NULL)

#3 /var/www/vams/protected/controllers/SystemSettingController.php(96): CActiveRecord->save()

#4 [internal function]: SystemSettingController->actionUpdate(‘22’)

#5 /var/www/yii/framework/web/actions/CInlineAction.php(47): ReflectionMethod->invokeArgs(Object(SystemSettingController), Array)

#6 /var/www/yii/framework/web/CController.php(300): CInlineAction->run()

#7 /var/www/yii/framework/web/filters/CFilterChain.php(133): CController->runAction(Object(CInlineAction))

#8 /var/www/yii/framework/web/filters/CFilter.php(41): CFilterChain->run()

#9 /var/www/yii/framework/web/CController.php(1049): CFilter->filter(Object(CFilterChain))

#10 /var/www/yii/framework/web/filters/CInlineFilter.php(59): CController->filterAccessControl(Object(CFilterChain))

#11 /var/www/yii/framework/web/filters/CFilterChain.php(130): CInlineFilter->filter(Object(CFilterChain))

#12 /var/www/yii/framework/web/CController.php(283): CFilterChain->run()

#13 /var/www/yii/framework/web/CController.php(257): CController->runActionWithFilters(Object(CInlineAction), Array)

#14 /var/www/yii/framework/web/CWebApplication.php(324): CController->run(‘update’)

2

this is from trace log with enableParamLogging enabled.

2010/10/19 15:58:50 [error] [system.db.CDbCommand] Error in executing SQL: UPDATE “SYSTEMSETTING” SET “SYSTEMSETTINGID”=:yp0, “KEY”=:yp1, “VALUE”=:yp2, “DTECREATED”=:yp3, “CREATEDBY”=:yp4, “DTEUPDATED”=:yp5, “UPDATEDBY”=:yp6 WHERE “SYSTEMSETTING”.“SYSTEMSETTINGID”=. Bind with parameter :yp0=‘1’, :yp1=‘aaa’, :yp2=‘bbb2’, :yp3=‘12-OCT-10’, :yp4=‘user’, :yp5=’’, :yp6=’’

3

Check the end of the select command… it says

WHERE "SYSTEMSETTING"."SYSTEMSETTINGID"=

And there is no value… that’s why the error “missing expression”

Can you post your actionUpdate() code here?

4

This is the generated actionUpdate() thanks.


/**

	 * Updates a particular model.

	 * If update is successful, the browser will be redirected to the 'view' page.

	 * @param integer $id the ID of the model to be updated

	 */

	public function actionUpdate($id)

	{

		$model=$this->loadModel($id);


		// Uncomment the following line if AJAX validation is needed

		// $this->performAjaxValidation($model);


		if(isset($_POST['SystemSetting']))

		{

			$model->attributes=$_POST['SystemSetting'];

			if($model->save())

				$this->redirect(array('view','id'=>$model->SYSTEMSETTINGID));

		}


		$this->render('update',array(

			'model'=>$model,

		));

	}
5

There is problem with this line in createInCondition function, I’m trying to trace backwards from here.


if($n===1)

                        {       Yii::trace('createInCondition 3 '.$values[0],'system.db.schema.CDbCommandBuilder');

				return $prefix.$column->rawName.($values[0]===null?' IS NULL':'='.$values[0]);

                        }
6

I don’t have oracel to try this… from your error stack you are calling UPDATE on row with primary key 22

so $values[0] should be 22 on there lines you posted above… from the logged sql seems that it’s empty… try to check it’s value at this point…

P.S. are you using Yii 1.1.4 ?

7

I commented out this line in createInCondition function and now everything works.

Somehow quotevalue function doesn’t work with Oracle?? I’m a noob in PHP…

Yes I’m using 1.1.4


foreach($values as &$value)

			{

				$value=$column->typecast($value);

				if(is_string($value))

                                {

                                        Yii::trace('createInCondition value='.$value,'system.db.schema.CDbCommandBuilder');

					//$value=$db->quoteValue($value);

                                        Yii::trace('createInCondition value='.$value,'system.db.schema.CDbCommandBuilder');

                                }

			}
8

Very strange…are you using Oracle XE?

9

Yes using Oracle XE.

10

I had the same problem and solved by extending the CDbConnection:




class COciConnection extends CDbConnection {

    protected function initConnection($pdo) {

        parent::initConnection($pdo);

        $stmt=$pdo->prepare('alter session set nls_date_format="dd/mm/yyyy"');

        $stmt->execute();

    }


    public function  quoteValue($str) {

        return "'".$str."'";

    }

}