+ Start a Discussion
SharathChandraSharathChandra 

Batch Apex Exception : First error: SQLException [common.exception.SfdcSqlException: ORA-01013: user requested cancel of current operation

Hi I'm facing the SQLException [common.exception.SfdcSqlException: ORA-01013 in my batch, i have used index field and selective filters in my query in batch but i'm not able to resolve the error. Can you please help me.

Thanks in advance.

Best Answer chosen by SharathChandra
Vinit_KumarVinit_Kumar
Then,the only option you have to contact support to get either of the field custom indexed.

All Answers

Vinit_KumarVinit_Kumar
The most possible reason is that the SOQL is running for a longer period.Can you verify the same by running the SOQL in SOQL tool like workbench or Force.com explorer.

Then based on that try optimising the same.
SharathChandraSharathChandra
Thanks for the reply. I tried to optimize the query and now my query looks like SELECT OpportunityId FROM OpportunityLineItem WHERE (Date__c > 2014-01-01 AND Date__c< 2014-02-01) AND ((Opportunity.StageName NOT IN ('Closed Won', 'Closed Lost') AND Opportunity.Contract_Type__c = 'Renew') OR (Opportunity.StageName IN ('Awaiting QA', 'Information Needed') AND Opportunity.Contract_Type__c IN ('New','Repeat'))) I tried to run the query in workbench in production it took more than 2 minutes for the first time and then after it took less than 2 minutes. Query is returning less than 1000 records. It is working fine in the Sandbox. How to optimize the above query. Please help.
Vinit_KumarVinit_Kumar
You have so many OR in where filters.Based on my observations,I can suggest you following :-

1.) Try reducing the Date range.
2.) Try to put some more filters in query
2.) Try contacting salesforce support to get Fields Contract_Type__c or Date__c  custom indexed.

Hope this helps !!
SharathChandraSharathChandra
Vinit, Is there any facility that ourselves can make the field custom indexed without contacting salesforce. Thanks!
Vinit_KumarVinit_Kumar
If you make the field as External Id on the field definition,then it gets automatically indexed.
SharathChandraSharathChandra
Thanks Vineet, One more thing I want the date range in query. And I can’t make date field as external id, so in order to make this custom index I need to contact salesforce right???
Vinit_KumarVinit_Kumar
That's right ,you need to contact support to get the Date field custom indexed.

Alternatively,why don't you make your field Contract_Type__c as external Id if it allows to make.
SharathChandraSharathChandra
It is a pick list field and we can’t make pick list field as external id.
Vinit_KumarVinit_Kumar
Then,the only option you have to contact support to get either of the field custom indexed.
This was selected as the best answer
SharathChandraSharathChandra
Thanks Vinit. I also want to tell you that our batch has executed for the first time properly but then after we are getting the ora-01013 excpetion. Regards, Sharathchandra Thukkani, Software Engineer Phone(O):91-20-66041700, Ext:7056