7

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

  • 1
    Can you share the case number where salesforce told you "Please engage your technical team to overcome this issue"? I have a friend in support I'd like to have a look at it. – ca_peterson Dec 8 '13 at 8:47
  • Just remove the where clause and make sure the error is happening because of where filter – Shebin Mathew Dec 8 '13 at 10:54
  • Out of interest was the String.escapeSingleQuotes() method being applied to the term variable? Ran into this issue today where that was the case. – Alan Morey Jun 18 '14 at 23:25
6

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.

| improve this answer | |
1

Try doing that concatenation in a variable instead of doing it in the query.

| improve this answer | |
  • makes no difference – user2610036 Dec 3 '13 at 23:00
  • 1
    whats the value of term? – PepeFloyd Dec 3 '13 at 23:05
  • 1
    Yeah, the SOQL works for me. Its most likely the value of term – BarCotter Dec 3 '13 at 23:13
0

Based on your updated answer where you specify term to be '123123\t22%'. If you are looking for a literal backslash in the Account Name then you will need to escape slashes twice in the term:

String term = '123123\\\\t22%';
System.debug('term: ' + term); // DEBUG|term: 123123\\t22%

This will result in an escaped backslash when passed to the query:

List<Account> records = [
    SELECT custom_field__c 
    FROM Account
    WHERE Name LIKE :term
    ORDER BY Name
    LIMIT 5
];
| improve this answer | |
0

I was getting the same error. The problem was that in my SOQL, I referenced a bad formula field. The formula was something like IF(ISNULL(GEOFIELD),False,True). If I removed that field from the query, everything was fine (NOTE: This was not in the where clause...just a selected field). My solution was to change the formula field to IF(ISBLANK(GEOFIELD.latitude__s) || ISBLANK(GEOFIELD.longitude__s,False,True).

The general issue I'm suggesting here is that I had a formula problem that the formula syntax check did not catch...or did not anticipate would cause a problem if used in a SOQL query. So check your formulas!

Hope this helps somebody debug their issues.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.