length of string value must be less than 4000 characters

Question

I am getting the below exception in debug logs when i am trying to enter more then 666 values separated by comma or enter(tried both) in a long text area field.

System.QueryException: length of string value must be less than 4000 characters

Please guide.

private static List<TargetingValue__c> getTargetingValues(List<sObject> sobjs) {
    Map<Id, TargetingAttribute__c> targetingAttr = getTargetingAttributes();
    // get mapping values
    String soql = 'SELECT TargetingAttr__c, DisplayOption__c, IMSMapping__c, TargetingAttr__r.Name, ControllingField__c, ControllingField2__c, ControllingField3__c, ControllingFieldValue__c, ControllingFieldValue2__c, ControllingFieldValue3__c FROM TargetingValue__c WHERE ';
    List<String> clauses = new List<String>();
    List<TargetingValue__c> targetingValues;
    if (targetingAttr!=null && targetingAttr.size()>0) {
        for(sobject o:sobjs){
            String cntrlfldvalue = String.valueOf(o.get('Country__c'));
            for(TargetingAttribute__c f:targetingAttr.values()){
                try{
                    String vallist = String.valueOf(o.get(f.Name));
                    if (vallist!=null && vallist.length()>0) {
                        List<String> sfvalst = vallist.split(';');
                        List<String> sfvals = new List<String>();
                        for(String s:sfvalst){
                            sfvals.add(string.escapeSingleQuotes(s.trim()));
                        }

                        String clause = '((ControllingField__c = NULL OR (ControllingField__c = \'Country__c\' AND ControllingFieldValue__c LIKE \'%'+cntrlfldvalue+'%\')) AND ';
                        clause += 'TargetingAttr__c = \''+f.Id+'\' AND ';
                        clause += 'DisplayOption__c IN (\'';
                        clause += String.join(sfvals,'\', \'');
                        clause += '\'))';
                        clauses.add(clause);
                    }
                }catch(Exception e){}
            }
        }

        if(clauses.size()>0){
            soql += '('+String.join(clauses,' OR ')+')';
            system.debug(soql);
            targetingValues = Database.query(soql);
        }
    }
    return targetingValues;
}

i have to match the value. Is there any way we can enter more characters in a field. Do we have any other field type which allow more characters than long text area ? Or can we upload a csv file which contain all the values ?

Answer 1

I can now reproduce this issue by using this code:

String soql = 'SELECT Id FROM Account WHERE Name = \'0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789z\'';
List<Account> accs = database.query(soql);

The exception is being thrown because the value part of one of the where clauses is more than 4000 chars in length.

Try using the LIKE operator and truncating the string before adding it to the soql query. Obviously this means you are only matching on the first 4000 chars rather than an exact match - if not I think you'll need to rethink the processes behind why you need to do an exact match on a value over 4000.