+ Start a Discussion
venkatasubhashkvenkatasubhashk 

Scheduled Batch Apex Error

Hi

 

I have a scheduled Batch Apex class scheduled for 1 Hr Interval to update or insert a Record into a custom Object from other Object

 

When i Scheduled it i am facing a New Error and my Job is Failing

 

The Error is "First error: SQLException [java.sql.SQLException: ORA-01013: user requested cancel of current operation"

 

any Ideas on this Error and to rectify it !

Best Answer chosen by Admin (Salesforce Developers) 
bob_buzzardbob_buzzard

That's a pretty low level error.  A few questions:

 

Are your batch jobs stacking up by any chance?  You can only have five queued or active at any one time, so it may be that when you try to start another one the first in the list is dropping off?

All Answers

bob_buzzardbob_buzzard

That's a pretty low level error.  A few questions:

 

Are your batch jobs stacking up by any chance?  You can only have five queued or active at any one time, so it may be that when you try to start another one the first in the list is dropping off?

This was selected as the best answer
venkatasubhashkvenkatasubhashk

I had Similar Jobs on same object  scheduled..so i scheduled only one Job to see error is rectified..but even for single job schedule i am getting the same Error...

 

I do not think its  stacking

 

 

bob_buzzardbob_buzzard

Is it possible to post your code?

venkatasubhashkvenkatasubhashk

hi I scheduled that again now ,its working,

Thank you so much..

 

But how can we give a good explanation or suggestion on this Error

bob_buzzardbob_buzzard
I'm not sure you can to be honest. These types of error would usually be trapped by Salesforce rather than allowed to bubble up to your code.
venkatasubhashkvenkatasubhashk

ok.

symantecAPsymantecAP

Hi Bob I have encountered same issue 

 

 

Hi I have an apex classes which is scheduled 1a.m everyday. The apex class aborts and gives the following error

 

First error: SQLException [java.sql.SQLException: ORA-01013: user requested cancel of current operation
: select /*AggJoin*/ "AccountId",
"Id",
"Subject",
"OwnerId",
"RecordType__c",
"Purpose__c",
"WhoId",
"ActivityDate",
"ActivityDate.AUX",

 

Here is my code. Kindly help 

/*
Extension case#03698934
This scheduler will consider only the following Events.
Events where last modify date is yesterday and End date is less then today AND
Profile of Assigned To  = Apartments.com House AC, Apartments.com RM User, Apartments.com Standard User, Gannett Standard User, LA Times Standard User AND
Event Record Type = Standard Event OR (Event Record Type = Event (Contact Optional) AND Level – Type = “Leases 4 Lunch”,” Mgmt Co – Cold Call Visit”,”Property – Cold Call Visit”)
and update the account info on from the selected Events information  

*/

global class ContactLastActivityDateBatch implements Database.Batchable<sObject>{
public String query;

global database.querylocator start(Database.BatchableContext BC){
    return Database.getQueryLocator(query);
}
    
global void execute(Database.BatchableContext BC, List<sObject> scope){
        List<Account> accList= new List<Account>();
     
       
        for(sObject s : scope){
        
        Account acc = (Account)s;
        System.debug('Rajan Testing date 1'+acc);
         if(acc.ActivityHistories.size() > 0 && (acc.Last_visit_date__c == null || acc.Last_visit_date__c <= acc.ActivityHistories[0].ActivityDate)){
            try{
                
               ActivityHistory act = acc.ActivityHistories;                 
               acc.Last_visit_subject__c = act.Subject;
               acc.Last_visit_date__c = act.ActivityDate;
               acc.Last_visit_assigned_to__c = act.Owner.Name;
               acc.Last_visit_record_type__c = act.RecordType__c;
               acc.Last_visit_purpose__c = act.Purpose__c;
               acc.Last_visit_level_type__c=act.Level_Type__c;
               acc.Last_visit_description__c=act.Description;
               
               //Populate only if who Id in Event is populated with a Contact Name.
               if(act.whoId != null && string.valueOf(act.whoId).startsWith('003')) 
               acc.Last_visit_contact_name__c = act.whoId;
               else
               acc.Last_visit_contact_name__c = null;
               //System.debug('Rajan Testing date 2'+c.Last_Activity_Date__c + act.ActivityDate);
                 accList.add(acc);             
            }
            catch(Exception ex){
                System.debug(ex.getMessage());
            }
        }
    }
    update accList;
    
    
    }
  global void finish(Database.BatchableContext BC){}  
}

 

/*Salesforce.com Extension#03698934
This class implements apex schedular interface to update Last Activity Date on Account
*/
global class updateAccountLastActivityDate implements Schedulable
{ 
    global void execute(SchedulableContext ctx){
        
        ContactLastActivityDateBatch accBatch = new ContactLastActivityDateBatch ();
    
        accBatch.query ='select Name, Last_visit_subject__c,Last_visit_level_type__c,Last_visit_description__c,Last_visit_date__c, Last_visit_assigned_to__c, Last_visit_record_type__c, Last_visit_purpose__c,  Last_visit_contact_name__c,'
                        +'(Select Subject,Owner.Name,RecordType__c,Purpose__c, whoId ,AccountId,id,ActivityDate,Level_Type__c,Description from ActivityHistories where '
                        +'Custom_Profile_Name__c in (\'Apartments.com House AC\',\'Apartments.com RM User\',\'Apartments.com Standard User\',\'Gannett Standard User\',\'LA Times Standard User\')'
                        +' AND (RecordType__c=\'Standard Event\'  OR  (RecordType__c=\'Event (Contact Optional)\' AND Level_Type__c in (\'Leases 4 Lunch\',\'Mgmt Co - Cold Call Visit\',\'Property - Cold Call Visit\')))'
                        +' AND ActivityDate <= TODAY'
                        +' AND Isdeleted =false'
                        +' order by ActivityDate desc limit 1) from Account';                                   
                        
                         
        ID batchprocessid = Database.executeBatch(accBatch);
       
    } 
bob_buzzardbob_buzzard

The last time I saw this kind of error message it was a bug in the platform.  Assuming you aren't cancelling the job, and you aren't trying to run more than the allowed number of batch jobs, you should raise this with Salesforce support.

symantecAPsymantecAP

Hi Bob

 

Thanks . I have contacted the Salesforce support and they came back with the following solution 

 



Hi Benjamin,

I have some updates from Tier-3, which I want to share with you.

As per investigation, below mentioned query which is in class “updateAccountLastActivityDate” is running long in the start method of the batch Apex job:

accBatch.query ='select Name, Last_visit_subject__c,Last_visit_level_type__c,Last_visit_description__c,Last_visit_date__c, Last_visit_assigned_to__c, Last_visit_record_type__c, Last_visit_purpose__c, Last_visit_contact_name__c,' 
+'(Select Subject,Owner.Name,RecordType__c,Purpose__c, whoId ,AccountId,id,ActivityDate,Level_Type__c,Description from ActivityHistories where ' 
+'Custom_Profile_Name__c in (\'Apartments.com House AC\',\'Apartments.com RM User\',\'Apartments.com Standard User\',\'Gannett Standard User\',\'LA Times Standard User\')' 
+' AND (RecordType__c=\'Standard Event\' OR (RecordType__c=\'Event (Contact Optional)\' AND Level_Type__c in (\'Leases 4 Lunch\',\'Mgmt Co - Cold Call Visit\',\'Property - Cold Call Visit\')))' 
+' AND ActivityDate <= TODAY' 
+' AND Isdeleted =false' 
+' order by ActivityDate desc limit 1) from Account';

Also, the way the query is setup; the results always return all the “Accounts” even when there is no “Activityhistory” record returned in the sub-query.


There are two possible solutions that may prevent the error.

1. In order to make this Query work better, try adding some filters in your query 

2. Another simple alternative solution to this issue will be, to schedule the same query to be executed after 10:00PM PST (in case of Apex) or 10:50 PM PST (in case of API), so that the query results are cached and when the batch apex job query runs, it will execute faster. 

 

 

Can you please throw some light on this. It would be great help . 

 

Thanks

Adil

bob_buzzardbob_buzzard

The first suggestion is where I'd start - can you add a where clause that will exclude accounts that don't have any activity history?  E.g. as you are mainly interested in last_visit information, can you exclude accounts that don't have that information populated?

symantecAPsymantecAP

Thanks Bob . This thing works great in Sandbox with no errors. Can you help me why is this happening. and also it works fine when it runs on sunday .it ran good last sunday and now on monday same issue

bob_buzzardbob_buzzard

You'll need to speak to Salesforce support to understand why it runs okay on particular days - it sounds like you get more resource at that time for some reason.   

 

Queries behave differently in different instances as the volumes of data are usually different - i.e. a dev sandbox can only have 10Mb of data, whereas a full copy could have a huge amount of data.  Thus if your query is too open ended it may end up pulling in all data regardless of whether it needs to.

symantecAPsymantecAP

Hi Bob 

 

Can you please help me with how we are excluding the accounts that doesnot have info populated from the query.Coz this thing is making me shiver. So please help me with the query 

 

 accBatch.query ='select Name, Last_visit_subject__c,Last_visit_level_type__c,Last_visit_description__c,Last_visit_date__c, Last_visit_assigned_to__c, Last_visit_record_type__c, Last_visit_purpose__c,  Last_visit_contact_name__c,'
                        +'(Select Subject,Owner.Name,RecordType__c,Purpose__c, whoId ,AccountId,id,ActivityDate,Level_Type__c,Description from ActivityHistories where '
                        +'Custom_Profile_Name__c in (\'Apartments.com House AC\',\'Apartments.com RM User\',\'Apartments.com Standard User\',\'Gannett Standard User\',\'LA Times Standard User\')'
                        +' AND (RecordType__c=\'Standard Event\'  OR  (RecordType__c=\'Event (Contact Optional)\' AND Level_Type__c in (\'Leases 4 Lunch\',\'Mgmt Co - Cold Call Visit\',\'Property - Cold Call Visit\')))'
                        +' AND ActivityDate <= TODAY'
                        +' AND Isdeleted =false'
                        +' order by ActivityDate desc limit 1) from Account';           

 

Thanks a lot in advance