Apex: Error ORA-01460

Question

I've developed an apex API on salesforce which performs a SOQL on a list of CSV data. It has been working smoothly until yesterday, after making a few changes to code that follow the SOQL query, I started getting a strange 500 error:

[{"errorCode":"APEX_ERROR","message":"System.UnexpectedException: common.exception.SfdcSqlException: ORA-01460: unimplemented or unreasonable conversion requested\n\n\nselect /SampledPrequery/ sum(term0) \"cnt0\",\nsum(term1) \"cnt1\",\ncount(*) \"totalcount\",\nsum(term0 * term1) \"combined\"\nfrom (select /*+ ordered use_nl(t_c1) /\n(case when (t_c1.deleted = '0') then 1 else 0 end) term0,\n(case when (upper(t_c1.val18) = ?) then 1 else 0 end) term1\nfrom (select /+ index(sampleTab AKENTITY_SAMPLE) */\nentity_id\nfrom core.entity_sample sampleTab\nwhere organization_id = '00Dq0000000AMfz'\nand key_prefix = ?\nand rownum <= ?) sampleTab,\ncore.custom_entity_data t_c1\nwhere t_c1.organization_id = '00Dq0000000AMfz'\nand t_c1.key_prefix = ?\nand sampleTab.entity_id = t_c1.custom_entity_data_id)\n\nClass.labFlows.queryContacts: line 13, column 1\nClass.labFlows.fhaQuery: line 6, column 1\nClass.zAPI.doPost: line 10, column 1"}]

the zAPI.doPost() is simply our router class which takes in the post payload as well as the requested operation. It then calls whatever function the operation requests. In this case, the call is to labFlows.queryContacts():

Public static Map<string,List<string>> queryContacts(string[] stringArray){

//First get the id to get to the associative entity, Contact_Deals__c id
List<Contact_Deals__c> dealQuery = [SELECT id, Deal__r.id, Deal__r.FHA_Number__c, Deal__r.Name, Deal__r.Owner.Name
                                 FROM Contact_Deals__c
                                 Where Deal__r.FHA_Number__c in :stringArray];

//Using the id in the associative entity, grab the contact information
List<Contact_Deals__c> contactQuery = [Select Contact__r.Name, Contact__r.Id, Contact__r.Owner.Name, Contact__r.Owner.Id, Contact__r.Rule_Class__c, Contact__r.Primary_Borrower_Y_N__c
                   FROM contact_deals__c
                   WHERE Id in :dealQuery];
//Grab all deal id's
Map<string,List<string>> result = new Map<string,List<string>>();
for(Contact_Deals__c i:dealQuery){
    List<string> temp = new list<string>();
    temp.add(i.Deal__r.Id);
    temp.add(i.Deal__r.Owner.Name);
    temp.add(i.Deal__r.FHA_Number__c);
    temp.add(i.Deal__r.Name);
    for(Contact_Deals__c j:contactQuery){
        if(j.id == i.id){
            //This doesn't really help if there are multiple primary borrowers on a deal - but that should be a SF worflow rule IMO
            if(j.Contact__r.Primary_Borrower_Y_N__c == 'Yes'){
                temp.add(j.Contact__r.Owner.Id);
                temp.add(j.Contact__r.Id);
                temp.add(j.Contact__r.Name);
                temp.add(j.Contact__r.Owner.Name);
                temp.add(j.Contact__r.Rule_Class__c);
                break;
            }
        }               
    }
    result.put(i.Deal__r.id, temp); 
}
return result;
}

The only thing I've changed is moving the temp list to add elements before the inner-loop (previously temp would only capture things from the inner-loop). The error above is referencing line 13, which is specifically the first SOQL call:

List<Contact_Deals__c> dealQuery = [SELECT id, Deal__r.id, Deal__r.FHA_Number__c, Deal__r.Name, Deal__r.Owner.Name
                                         FROM Contact_Deals__c
                                         Where Deal__r.FHA_Number__c in :stringArray];

I've tested this function in the apex anonymous window and it worked perfectly:

string a = '00035398,00035401';
string result = zAPI.doPost(a, 'fhaQuery');
system.debug(result);

Results:

13:36:54:947 USER_DEBUG [5]|DEBUG|{"a09d000000HRvBAD":["a09d000000HRvBAD","Contacta","11111111","Plaza Center Apts"],"a09d000000HsVAD":["a09d000000HsVAD","Contactb","22222222","The Garden"]}

So this is working. The next part is maybe looking at my python script that is calling the API,

def origQuery(file_name, operation):
    csv_text = ""
    with open(file_name) as csvfile:
        reader = csv.reader(csvfile, dialect='excel')
        for row in reader:
            csv_text += row[0]+','
            csv_text = csv_text[:-1]

    data = json.dumps({
        'data' : csv_text,
        'operation' : operation
    })


    results = requests.post(url, headers=headers, data=data) 
    print results.text

origQuery('myfile.csv', 'fhaQuery')

I've tried looking up this ORA-01460 apex error, but I can't find anything that will help me fix this issue.

Can any one shed ore light on what this error is telling me?

Thank you all so much!

Answer 1

Report this error to salesforce.com support. Oracle errors should never surface in normal use, so if you do receive an error like this, it's a bug that should be filed with support so salesforce.com's developers can fix it.

Answer 2

It turns out the error was in the PY script. For some reason the following code isn't functioning as it is supposed to:

with open(file_name) as csvfile:
        reader = csv.reader(csvfile, dialect='excel')
        for row in reader:
            csv_text += row[0]+','
            csv_text = csv_text[:-1]

This was returning one very long string that had zero delimiters. The final line in the code was cutting off the delimiter. What I needed instead was:

with open(file_name) as csvfile:
    reader = csv.reader(csvfile, dialect='excel')
    for row in reader:
        csv_text += row[0]+','
    csv_text = csv_text[:-1]

Which would cut off the final ','

The error was occurring because the single long string was above 4,000 characters.