Error ORA-01036: ADO.NET Timestamp and Oracle

[Alejandro Bibiano González]

I can't execute ExecuteNonQuery if I use an parameter OracleType.Timestamp.
I have an User object with a field that store the timestamp from the row. Te
field is DataTime type, and on the database (Oralce 9.2.0) it's Timestamp.

The code to do the update is:

OracleParameter [] parameter = new OracleParameter[7];
string update = @"UPDATE USU SET USU_USUARIO = :usuario,USU_CLAVE = :clave,
USU_NOMBRE = :nombre, USU_APELLIDOS = :apellidos,USU_BAJA = :esbaja
WHERE USU_ID = :idUsuario AND USU_TIMESTAMP = :timestamp";
parameter[0] = new OracleParameter("usuario", OracleType.VarChar, 10);
parameter[0].Value = usuario.UsuarioLogin;
parameter[1] = new OracleParameter("clave", OracleType.VarChar, 10);
parameter[1].Value = usuario.Clave;
parameter[2] = new OracleParameter("nombre", OracleType.VarChar, 25);
parameter[2].Value = usuario.Nombre;
parameter[3] = new OracleParameter("apellidos", OracleType.VarChar, 50);
parameter[3].Value = usuario.Apellidos;
parameter[4] = new OracleParameter("baja", OracleType.Number, 1);
parameter[4].Value = (usuario.EsBaja) ? 1:0;
parameter[5] = new OracleParameter("idUsuario", OracleType.Number);
parameter[5].Value = usuario.IdUsuario;
parameter[6] = new OracleParameter("timestamp", OracleType.Timestamp);
parameter[6].Value = usuario.TimeStamp;
try
{
if(OracleHelper.ExecuteNonQuery(Global.CadenaConexion, CommandType.Text,
update, parameter) > 0)
{
return true;
}
else
{
return false;
}
}
catch(OracleException ex)
{
throw new ExcepcionBaseDatos(ex.Code + ex.Message);
}
catch(Exception)
{
throw new Exception("Error al actualizar datos");
}

The error is ORA-01036

Thanks,

Alex Bibiano

 

[Paul Clement]

¤ I can't execute ExecuteNonQuery if I use an parameter OracleType.Timestamp.
¤ I have an User object with a field that store the timestamp from the row. Te
¤ field is DataTime type, and on the database (Oralce 9.2.0) it's Timestamp.
¤
¤ The code to do the update is:
¤
¤ OracleParameter [] parameter = new OracleParameter[7];
¤ string update = @"UPDATE USU SET USU_USUARIO = :usuario,USU_CLAVE = :clave,
¤ USU_NOMBRE = :nombre, USU_APELLIDOS = :apellidos,USU_BAJA = :esbaja
¤ WHERE USU_ID = :idUsuario AND USU_TIMESTAMP = :timestamp";
¤ parameter[0] = new OracleParameter("usuario", OracleType.VarChar, 10);
¤ parameter[0].Value = usuario.UsuarioLogin;
¤ parameter[1] = new OracleParameter("clave", OracleType.VarChar, 10);
¤ parameter[1].Value = usuario.Clave;
¤ parameter[2] = new OracleParameter("nombre", OracleType.VarChar, 25);
¤ parameter[2].Value = usuario.Nombre;
¤ parameter[3] = new OracleParameter("apellidos", OracleType.VarChar, 50);
¤ parameter[3].Value = usuario.Apellidos;
¤ parameter[4] = new OracleParameter("baja", OracleType.Number, 1);
¤ parameter[4].Value = (usuario.EsBaja) ? 1:0;
¤ parameter[5] = new OracleParameter("idUsuario", OracleType.Number);
¤ parameter[5].Value = usuario.IdUsuario;
¤ parameter[6] = new OracleParameter("timestamp", OracleType.Timestamp);
¤ parameter[6].Value = usuario.TimeStamp;
¤ try
¤ {
¤ if(OracleHelper.ExecuteNonQuery(Global.CadenaConexion, CommandType.Text,
¤ update, parameter) > 0)
¤ {
¤ return true;
¤ }
¤ else
¤ {
¤ return false;
¤ }
¤ }
¤ catch(OracleException ex)
¤ {
¤ throw new ExcepcionBaseDatos(ex.Code + ex.Message);
¤ }
¤ catch(Exception)
¤ {
¤ throw new Exception("Error al actualizar datos");
¤ }
¤
¤ The error is ORA-01036

Timestamp is reserved word in Oracle. Have you tried changing the parameter name?

In addition, if you don't need the value of the time stamp you could call the Sysdate function from
your stored procedure instead of passing it in as a parameter.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)