[SalesForce] getting an oracle exception when doing a SOQL query

the error I get

caused by: System.UnexpectedException: common.exception.SfdcSqlException:
ORA-01424: missing or illegal character following the escape character

select /*gatherSlowStats*/ count(1)
from core.custom_index_value ind
where (ind.string_value like ? ESCAPE '\')
and (ind.organization_id = ?)
and (ind.key_prefix = ?
and (ind.deleted = '0'))
and (ind.index_num = ?)
and rownum <= ?

The supposed culprit code according the the exception

List<Account> records = [Select custom_field__c From Account where Name
    like :(term + '%') order by Name limit 5];

Is there some special sanitising of strings that I need to do to prevent this error?

edit:
Updated my code with the suggestions and ran it with full debug logs

String term = '123123\t22%';
System.debug('term: ' + term);
List<Account> records = [Select custom_field__c From Account where Name
    like :term order by Name limit 5];

16:43:42.047 (47502000)|SYSTEM_METHOD_ENTRY|[16]|System.debug(ANY)
16:43:42.047 (47519000)|USER_DEBUG|[16]|DEBUG|term: 123123\t22%
16:43:42.047 (47526000)|SYSTEM_METHOD_EXIT|[16]|System.debug(ANY)
16:43:42.051 (51810000)|SOQL_EXECUTE_BEGIN|[17]|Aggregations:0|select custom_field__c from Account where Name like :tmpVar1 order by Name limit 5
16:43:42.078 (78747000)|EXCEPTION_THROWN|[17]|System.UnexpectedException: common.exception.SfdcSqlException: ORA-01424: missing or illegal character following the escape character


select /*Apex.Class.myController.doSearch: line 17*/ /*+ ordered use_nl(cft) */
cft.val132 "custom_field__c",
nest."Name",
nest."Id",
nest."RecordTypeId"
from (select *
from (select *
from (select t.name "Name",
upper(t.name) "_SORT_COL_0",
t.account_id "Id",
t.record_type_id "RecordTypeId",
t.account_id repl__0
from sales.account t
where (t.organization_id = '00DN00000003ALJ')
and (t.deleted = '0')
and (t.account_id != '000000000000000'))
where (upper("Name") like ? ESCAPE '\')
order by "_SORT_COL_0" asc nulls first)
where rownum <= ?) nest,
sales.account_cfdata cft
where (cft.account_cfdata_id = repl__0)
and (cft.organization_id = '00DN00000003ALJ')
16:43:42.078 (78880000)|SYSTEM_MODE_EXIT|false
16:43:42.078 (78964000)|FATAL_ERROR|System.UnexpectedException: common.exception.SfdcSqlException: ORA-01424: missing or illegal character following the escape character

update:

salesforce replied with: Please engage your technical team to overcome this issue

Best Answer

I've tried this in my development org 

String term = 'Burlington Textiles Corp of America';
List<Account> records = [Select Active__c From Account where Name like :(term + '%') order by Name limit 5];

It works fine for me, is there something odd in your 'term' value?

Some things to try...

Avoid the concatination in the SOQL...

String termForSOQL = term + '%';
List<Account> records = [Select Active__c From Account where Name like :termForSOQL order by Name limit 5];

Add the following to see 'term' value...

System.debug(term);

Also try ....

List<Account> records = 
   [Select Active__c From Account where Name like :'Burlington Textiles Corp of America%') 
     order by Name limit 5];

(e.g. without the variable)

Bottom Line. Basically this should not be happening normally, you've encountered a Salesforce platform bug. You should make a small peace of Apex like the above with the value of 'term' hard coded and report the issue to Salesforce support. My guess is there is something special, a special character or so? Also tell Salesforce what your Org Id is.

Related Topic