# ActiveRecord::StatementInvalid in update method using Oracle **URL:** \ **Category:** Rails\ **Created:** [October 3, 2007, 5:50am UTC](https://www.ruby-forum.com/t/activerecord-statementinvalid-in-update-method-using-oracle/116172 "2007-10-03T05:50:37Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![fotn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/3ab097/32.png) [@fotn](https://www.ruby-forum.com/u/fotn)\ **Post date:** [October 3, 2007, 5:50am UTC](https://www.ruby-forum.com/t/activerecord-statementinvalid-in-update-method-using-oracle/116172/1 "2007-10-03T05:50:37Z")
Sorry about the length, but it includes what has already been tried and I’m at my wits end… I have in production a RoR app which utilises an existing Oracle database. The app has its own user which has views, which updates the underlying tables using triggers, so that I can maintain RoR conventions for column names, etc. Recently, when attempting to update information, it has been throwing an ActiveRecord::StatementInvalid exception, by way of ‘ORA-01861: literal does not match string format’. First: checked the format of the fields. The dates in the SQL statement on the error page do not match the Oracle standard, but this doesn’t seem to be the underlying cause. There are callbacks in place to ensure that the date fields in use are of the correct data type before saving. So it would appear that it’s not the format of the data, even if that’s what Oracle claims. (Further reasons for this conclusion are explained below.) Next: the error ONLY occurs on the production servers (which utilise load-sharing), never on the test server (single machine). For a time it will occur more on one server than the other. Unfortunately, the problem will then swap to the other server and the cycle repeats itself. Finally: The problem is intermittent (but getting worse). I have run multiple tests through jMeter on both the production servers and our test server and not been able to reproduce the error even under high loading conditions. Nor have I been able to reproduce the error manually on the test server through the app – only on the production servers. (I have been using the production database in all instances). Note: in the jMeter tests, I have tested each of [http://host1/app](http://host1/app), [http://host2/app](http://host2/app) and [http://www/app](http://www/app) (fully qualified in all cases) where host1 and host2 are the individual production servers. So: In spite of what Oracle claims, it would appear that it is not a formatting error, nor is it the load-sharing mechanism, and it is impossible to replicate the error under test conditions. The only time it occurs is with the production database on the production servers. There has not been a similar problem with any other apps (Rails or otherwise), either – only this one. The other apps do not update the same information, or as frequently, however. Has anyone had a similar problem with Oracle/RoR (or even with another database)? As an interim solution, I have included a rescue statement for the exception, which re-attempts to update the information. If it fails another 5 times, it fails more gracefully. I am interested in a more definitive solution, however. Example: ActiveRecord::StatementInvalid in […]Controller#update OCIError: ORA-01861: literal does not match format string: UPDATE [table\_name] SET […], updated\_at = ‘2007-10-02 09:36:59’, […], date\_of\_birth = ‘2007-10-02 00:00:00’, […], member\_since = ‘2007-10-02 00:00:00’, […] WHERE id = ‘1’ Sorry about having to cut the other fields, I realise it makes it harder; however, much of the information is sensitive. I have been through each of these fields individually to see which cause the problems if I cut and paste into Oracle – only the dates had a problem doing this directly; all other fields are fine. I look forward to hearing some new suggestions as to how I might be able to solve this problem. Thanks. ---
**Author:** ![fotn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/3ab097/32.png) [@fotn](https://www.ruby-forum.com/u/fotn)\ **Post date:** [February 22, 2008, 11:58am UTC](https://www.ruby-forum.com/t/activerecord-statementinvalid-in-update-method-using-oracle/116172/2 "2008-02-22T11:58:49Z")
Hello there, We’re experiencing exactly the same problem: an ORA-01861 error when performing an update that intermittently fails in production, but not on development or staging servers. A quick question: do you connect to multiple databases from this application? Have you found a fix? Which version of Rails are you rolling on? ~ M fotn wrote: > Sorry about the length, but it includes what has already been tried > and I’m at my wits end… > > I have in production a RoR app which utilises an existing Oracle > database. The app has its own user which has views, which updates the > underlying tables using triggers, so that I can maintain RoR > conventions for column names, etc. Recently, when attempting to update > information, it has been throwing an ActiveRecord::StatementInvalid > exception, by way of ‘ORA-01861: literal does not match string > format’. > > First: checked the format of the fields. The dates in the SQL > statement on the error page do not match the Oracle standard, but this > doesn’t seem to be the underlying cause. There are callbacks in place > to ensure that the date fields in use are of the correct data type > before saving. So it would appear that it’s not the format of the > data, even if that’s what Oracle claims. (Further reasons for this > conclusion are explained below.) > > Next: the error ONLY occurs on the production servers (which utilise > load-sharing), never on the test server (single machine). For a time > it will occur more on one server than the other. Unfortunately, the > problem will then swap to the other server and the cycle repeats > itself. > > Finally: The problem is intermittent (but getting worse). I have run > multiple tests through jMeter on both the production servers and our > test server and not been able to reproduce the error even under high > loading conditions. Nor have I been able to reproduce the error > manually on the test server through the app – only on the production > servers. (I have been using the production database in all instances). > Note: in the jMeter tests, I have tested each of [http://host1/app](http://host1/app), > [http://host2/app](http://host2/app) and [http://www/app](http://www/app) (fully qualified in all cases) > where host1 and host2 are the individual production servers. > > So: In spite of what Oracle claims, it would appear that it is not a > formatting error, nor is it the load-sharing mechanism, and it is > impossible to replicate the error under test conditions. The only time > it occurs is with the production database on the production servers. > There has not been a similar problem with any other apps (Rails or > otherwise), either – only this one. The other apps do not update the > same information, or as frequently, however. > > Has anyone had a similar problem with Oracle/RoR (or even with another > database)? > > As an interim solution, I have included a rescue statement for the > exception, which re-attempts to update the information. If it fails > another 5 times, it fails more gracefully. I am interested in a more > definitive solution, however. > > Example: > > ActiveRecord::StatementInvalid in […]Controller#update > OCIError: ORA-01861: literal does not match format string: UPDATE > [table\_name] SET […], updated\_at = ‘2007-10-02 09:36:59’, […], > date\_of\_birth = ‘2007-10-02 00:00:00’, […], member\_since = > ‘2007-10-02 00:00:00’, […] WHERE id = ‘1’ > > Sorry about having to cut the other fields, I realise it makes it > harder; however, much of the information is sensitive. I have been > through each of these fields individually to see which cause the > problems if I cut and paste into Oracle – only the dates had a > problem doing this directly; all other fields are fine. > > I look forward to hearing some new suggestions as to how I might be > able to solve this problem. Thanks. ---
**Author:** ![fotn](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/f/3ab097/32.png) [@fotn](https://www.ruby-forum.com/u/fotn)\ **Post date:** [March 20, 2008, 3:48am UTC](https://www.ruby-forum.com/t/activerecord-statementinvalid-in-update-method-using-oracle/116172/3 "2008-03-20T03:48:26Z")
> Which version of Rails are you rolling on? Initially we were using Rails 1.1.6 with Ruby 1.8.4, ruby-oci8 adapter 0.1.6 We have recently upgraded to Rails 2.0.2, Ruby 1.8.6, ruby-oci8 adapter 1.0.0 and activerecord-oracle-adapter The issue is ostensibly a failure to convert the date/datetime string to the correct oracle format. The initial context of this error seems to be resolved; however, we had the problem reappear (in a different application) when using the activerecord\_store session (it has been remedied for the time being by returning to cookie session store). > do you connect to multiple databases from this application? The production databases are load balanced on two servers; however, each Rails application only talks to a single instance, e.g. if the tnsnames.ora has an entry for PROD which load balances server1 and server2, and a database prd, the entry in the database.yml file is database: server1:1521/prd so it always talks to a single instance of the production database, even though the databases themselves are load-balanced. > Have you found a fix? Not directly; however, upgrading to Rails 2.0.2 seems to have improved the situation, even if it has not remedied it entirely. On Feb 22, 8:58 pm, Matt W. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)