Provide table script and sample data
Actually both are not tables but views and providing sample data is not so easy.
But the weak point in the query is that mcsearchvalue is a NVARCHAR2(60) and mskey is a NUMBER. This is the only collision point in the query.
But as I already stated the query is working just fine with any other than this number.
Also it is working even for this number if we change the mcsearchvalue with mcvalue NVARCHAR2(200). Both columns contain the same values only there is an index on mcsearchvalue and no index on mcvalue.
But I don't know about That.
Lets see any other give solution for this?
Oracle is probably doing a conversion of mcsearchvalue to NUMBER since you are asking the database to look for it among numbers. If one of the rows has a value for mcsearchvalue that cannot be converted to NUMBER you will get that error. Now, you may think that it is strange that it sometimes works if you change the number value, but that may come from the fact that the optimizer (CBO) creates another execution plan based on the statistics on the table. In SQL Developer you can hit F10 key to see if the execution plan changes from one query to another (though one is not 100% guarantee that it will be executed with the shown plan.)
I think you can get away from this problem by doing an explicit conversion with: TO_NCHAR(mskey)
Lastly, there is probably nothing wrong with the index, I can't remember last time I saw a corrupt one