Monday, September 13, 2010

Active Record and ORA-00932: inconsistent datatypes: expected - got CLOB

CLOBs can be problematic in Rails, and yet they are the default datatype for ActiveRecord text type (used for textareas) when used with Oracle. With the various named scopes, etc. you can use with ActiveRecord, even seemingly simple code like:
(@user.your_models && @user.your_models.length > 0)
with this code in YourModel:
  default_scope :conditions => {:another_model_id => AnotherModel.something.id, :deleted_at => nil}
  belongs_to :another_model

  has_many :some_join_models  
  has_many :users, :through => :some_join_models, :uniq => true
can cause errors like:
OCIError: ORA-00932: inconsistent datatypes: expected - got CLOB: SELECT DISTINCT your_models.* FROM your_models  INNER JOIN some_join_models ON your_models.id = some_join_models.your_model_id    WHERE ((some_join_models.user_id = 1234)) AND (your_models.deleted_at IS NULL AND your_models.another_model_id = 6789) 

RAILS_ROOT: ...
Application Trace | Framework Trace | Full Trace

.../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract_adapter.rb:212:in `log'
.../vendor/gems/activerecord-oracle_enhanced-adapter-1.2.0/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:927:in `select'
.../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache'
.../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in `select_all'
.../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:81:in `cache_sql'
.../vendor/rails/activerecord/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in `select_all'
.../vendor/rails/activerecord/lib/active_record/base.rb:661:in `find_by_sql'
.../vendor/rails/activerecord/lib/active_record/base.rb:1553:in `find_every'
.../vendor/rails/activerecord/lib/active_record/base.rb:615:in `find'
.../vendor/rails/activerecord/lib/active_record/associations/has_many_through_association.rb:73:in `find_target'
.../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:349:in `load_target'
.../vendor/rails/activerecord/lib/active_record/associations/association_collection.rb:283:in `length'
.../app/controllers/application_controller.rb:34:in `a_method_that_just_reference_all_models'
.../app/controllers/application_controller.rb:12:in `a_method'

So as a first thought, why not convert the CLOB to VARCHAR2? It's faster to access. But, since many CRUD UI and form helper tools assume that you are using text for textarea and string for text input, if you change a text type to a string type, you might have more complexity in displaying the textarea as you need it to for that field. If you want to convert to VARCHAR2 anyway, see this snippet. However, you might instead look into how you can adjust things to specify references to the CLOB column as "DBMS_LOB.SUBSTR(name_of_column)". We used that previously to get sort_by in our CRUD tool working.

The problem was the use of uniq => true in all related models. It was there to try to keep additional associations from being displayed that were duplicate, but it wasn't even doing that correctly (probably because of the unique id on the association model, SomeJoinModel, making all association records unique).

No comments: