#sqlexception

[rabatfirstsight]

SQLException ORA-01502 partition of such index is in unusable state

reference: https://dba.stackexchange.com/questions/3754/ora-01502-index-or-partition-of-such-index-is-in-usable-state-problem

for DBA:

SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;' FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';

for non-DBA:

SELECT 'ALTER INDEX ' || INDEX_NAME || ' REBUILD;' FROM USER_INDEXES WHERE STATUS = 'UNUSABLE';

[henrikwiberg]

SQLException: Near "," – A Rails .where multiple values problem.

ActionView::Template::Error (SQLite3::SQLException: near ",": syntax error:

If this is an error you get, this post might help you out.

I learned this lesson while trying to query the database for posts using an array of foreign keys I've gathered from a list of checkboxes.

In my list each checkbox carries a value, a foreign key, mapping to posts belonging to that key. What I want is for the database to fetch all posts corresponding to the selected array of id's.

Your code could look something like this:

<%= check_box_tag "source_ids[]", source.id %>

And your controller query might look something like this:

@posts = Post.where("source_id = ?", params[:source_ids])

What happens here is that everything is fine, as long as your params[:source_ids] only carry one value. That allows source_id to be set. If it carries multiple values, i.e.:

SELECT "posts".* FROM "posts"  WHERE (source_id = '2','3' )

The database doesn't know what value to choose from, 2 or 3. And the error in the beginning of the post occur.

The solution

The solution to this problem is a semantic one, instead of telling SQL that you will be giving it a value for the source_id, you have to tell it to look in an array of values.

@posts = Post.where("source_id = (?)", params[:source_ids])

Note: There's parentheses around ?