Default Scopes with Oracle

If you have the unfortunate experience of having to deal with Oracle databases you might run into an interesting problem with default scopes where Oracle spits back the following error:

ORA-00933: SQL command not properly ended: UPDATE post SET post_gid = NULL WHERE ((post_gid = 2060)) ORDER BY name

The problem, if you guessed it, is that you can’t add an ORDER clause to the end of an UPDATE statement in SQL. It is invalid syntax. This happens because you might have done something like this in your Post model:

class Post < ActiveRecord::Base
# Named Scopes
default_scope :order => "name"
end

I really like to use default_scope on my models when apt. However, with Oracle, you might as well forget about that until Rails is patched (which I suppose I should do once this aggravates me more). The workaround is to not use default scopes but, instead, used named scopes, define them as conditions to your finds (usually index actions), and/or has_many associations. For example:

Named Scope

class Post < ActiveRecord::Base
# Named Scopes
named_scope :default, :order => "name"
end

This will work nicely but if you need to pass additional conditions, you’ll still need to do the following:

Post.default.all(:conditions => {:name => "Once Upon a Time"})

Index Action

Post.all :order => "name"

Has Many Association

class User < ActiveRecord::Base
# Associations
has_many :posts, :order => "name"
end

Obviously, not as elegant as defining them in one location but it’ll get you by.

3 Comments to Default Scopes with Oracle

1. 

   Looks like a perfect opportunity to patch ActiveRecord to add :only and :except clauses!
Kevin McFadden on June 10th, 2009
2. 

   Yeah, might have to look into that. I’ve seen traffic in the Rails threads of people wanting :only and :except clauses as well. In the case above, I would think it could be done intelligently based on the type of RESTful action.
Brooke Kuhlmann on June 10th, 2009
3. 

   Post Update: Updated the original post to include mention of the “named_scope :default” scope since this is also a useful trick or one I used to use before the default_scope capability was added.
Brooke Kuhlmann on June 10th, 2009

Leave a comment

You must be logged in to post a comment.