timer job error in advance query ORA-01013 user request cancel of current operation

Hi All, 

i have timer job to generate csv report and send via email, when i try to run the timer job in production (dev and uat working fine) having the following exception, please help asap. 


Error Detail
Back to Log
Id:
21d322a9-43a1-4177-a5ad-4604936de3a1
Time of Log:
29/01/2019 09:18:38
eSpace:
WhpEmails
Tenant:
Users
User:
Session Id:
f9bb20ba-c1fd-4af2-acc3-f1ae1de3f991
Server:
BDLXAS50
Module:
Message:
Error executing query. Error in advanced query PendingReport in Preparation in PendingWINSapplications in Emails in WhpEmails (SELECT  {Application}.[ReferenceNo],           COALESCE(TRIM({Applicant}.[FIN]), '-'),           {Applicant}.[FullName],           COALESCE(TRIM({Coy}.[UEN]), '-'),          COALESCE(TRIM({Coy}.[CompanyCode]), '-'),          to_char({Application}.[DOA], 'dd/MM/yyyy') as DOA,          COALESCE(TRIM({PassType}.[Label]), '-') as PassType,          CASE               WHEN {Application}.[ApplicationType] = 'APPEAL' THEN 'Application'               WHEN {Application}.[ApplicationType] = 'APL_RENEW' THEN 'Renewal'               ELSE {ApplicationType}.[Label] END as ApplicationType,          COALESCE(TRIM(APEL.[AppealIndicator]), 'N') as AppealIndicator,          {ProcessingStage}.[Label] as Stage,          COALESCE(TRIM(PR.[Value]), '-') as ReasonPending,          COALESCE(TRIM({BusinessSector}.[Label]), '-') as Sector,          COALESCE(TRIM({User}.[Name]), '-') as Officer,          COALESCE(TRIM({Evaluation}.[Remarks]), '-') as CORemarks,          COALESCE(TRIM(MPASS.[MainPassApplicationNo] ...): ORA-01013: user requested cancel of current operation
Environment Information
eSpaceVer: 10 (Id=452, PubId=983, CompiledWith=10.0.912.0)
RequestUrl: https://127.0.0.1/WhpEmails/PendingWINSapplications.jsf (Method: POST)
ClassLoader: ModuleClassLoader for Module "deployment.WhpEmails.war:main" from Service Module Loader(9679380)
FilePath: /WhpEmails/PendingWINSapplications.jsp
ClientIp: 127.0.0.1
Locale: en-US
DateFormat: dd/MM/yyyy
PID: 2171 ('2171@BDLXAS50', Started='1/25/19 8:53:12 PM', Priv=1820Mb, Virt=7969Mb)
TID: 2317
Thread Name: http-0.0.0.0:8080-24
JRE: 25.191-b26

Stack:
[1] outsystems.hubedition.runtimeplatform.DataBaseException: Error executing query.
   at oswhpemails.managedbean.flowemails.ScrnPendingWINSapplications$FuncssPreparation.queryPendingReport(Unknown Source)
   at oswhpemails.managedbean.flowemails.ScrnPendingWINSapplications.preparation(Unknown Source)
   at oswhpemails.managedbean.flowemails.ScrnPendingWINSapplications.pageLoad(Unknown Source)
   at outsystems.hubedition.webwidgets.uicomponent.utils.ComponentUtils.invokeBeanPageLoad(Unknown Source)
   at outsystems.hubedition.webwidgets.uicomponent.os_controls.PageComponent.encodeBegin(Unknown Source)
   at javax.faces.component.UIComponent.encodeAll(Unknown Source)
   at javax.faces.component.UIComponent.encodeAll(Unknown Source)
   at com.sun.faces.application.ViewHandlerImpl.doRenderView(Unknown Source)
   at com.sun.faces.application.ViewHandlerImpl.renderView(Unknown Source)
   at com.sun.faces.lifecycle.RenderResponsePhase.execute(Unknown Source)
   at com.sun.faces.lifecycle.Phase.doPhase(Unknown Source)
   at com.sun.faces.lifecycle.LifecycleImpl.render(Unknown Source)
   at javax.faces.webapp.FacesServlet.service(Unknown Source)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
   at outsystems.hubedition.runtimeplatform.email.EmailUtils.handleEmail(Unknown Source)
   at outsystems.hubedition.webwidgets.BaseRequestStartupFilter.doFilter(Unknown Source)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
   at outsystems.hubedition.webwidgets.filters.HttpContextFilter.doFilter(Unknown Source)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
   at outsystems.hubedition.webwidgets.filters.CacheControlFilter.doFilter(Unknown Source)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149)
   at org.jboss.as.web.security.SubjectInfoSetupValve.invoke(SubjectInfoSetupValve.java:34)
   at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:151)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97)
   at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:560)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:656)
   at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:926)
   at java.lang.Thread.run(Thread.java:748)

[2] outsystems.hubedition.runtimeplatform.DataBaseException: Error in advanced query PendingReport in Preparation in PendingWINSapplications in Emails in WhpEmails (SELECT  {Application}.[ReferenceNo],           COALESCE(TRIM({Applicant}.[FIN]), '-'),           {Applicant}.[FullName],           COALESCE(TRIM({Coy}.[UEN]), '-'),          COALESCE(TRIM({Coy}.[CompanyCode]), '-'),          to_char({Application}.[DOA], 'dd/MM/yyyy') as DOA,          COALESCE(TRIM({PassType}.[Label]), '-') as PassType,          CASE               WHEN {Application}.[ApplicationType] = 'APPEAL' THEN 'Application'               WHEN {Application}.[ApplicationType] = 'APL_RENEW' THEN 'Renewal'               ELSE {ApplicationType}.[Label] END as ApplicationType,          COALESCE(TRIM(APEL.[AppealIndicator]), 'N') as AppealIndicator,          {ProcessingStage}.[Label] as Stage,          COALESCE(TRIM(PR.[Value]), '-') as ReasonPending,          COALESCE(TRIM({BusinessSector}.[Label]), '-') as Sector,          COALESCE(TRIM({User}.[Name]), '-') as Officer,          COALESCE(TRIM({Evaluation}.[Remarks]), '-') as CORemarks,          COALESCE(TRIM(MPASS.[MainPassApplicationNo] ...): ORA-01013: user requested cancel of current operation

   at oswhpemails.managedbean.flowemails.ScrnPendingWINSapplications$FuncssPreparation.queryPendingReport(Unknown Source)
   at oswhpemails.managedbean.flowemails.ScrnPendingWINSapplications.preparation(Unknown Source)
   at oswhpemails.managedbean.flowemails.ScrnPendingWINSapplications.pageLoad(Unknown Source)
   at outsystems.hubedition.webwidgets.uicomponent.utils.ComponentUtils.invokeBeanPageLoad(Unknown Source)
   at outsystems.hubedition.webwidgets.uicomponent.os_controls.PageComponent.encodeBegin(Unknown Source)
   at javax.faces.component.UIComponent.encodeAll(Unknown Source)
   at javax.faces.component.UIComponent.encodeAll(Unknown Source)
   at com.sun.faces.application.ViewHandlerImpl.doRenderView(Unknown Source)
   at com.sun.faces.application.ViewHandlerImpl.renderView(Unknown Source)
   at com.sun.faces.lifecycle.RenderResponsePhase.execute(Unknown Source)
   at com.sun.faces.lifecycle.Phase.doPhase(Unknown Source)
   at com.sun.faces.lifecycle.LifecycleImpl.render(Unknown Source)
   at javax.faces.webapp.FacesServlet.service(Unknown Source)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:295)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
   at outsystems.hubedition.runtimeplatform.email.EmailUtils.handleEmail(Unknown Source)
   at outsystems.hubedition.webwidgets.BaseRequestStartupFilter.doFilter(Unknown Source)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
   at outsystems.hubedition.webwidgets.filters.HttpContextFilter.doFilter(Unknown Source)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
   at outsystems.hubedition.webwidgets.filters.CacheControlFilter.doFilter(Unknown Source)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:246)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:214)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:231)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:149)
   at org.jboss.as.web.security.SubjectInfoSetupValve.invoke(SubjectInfoSetupValve.java:34)
   at org.jboss.as.web.security.SecurityContextAssociationValve.invoke(SecurityContextAssociationValve.java:169)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:151)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:97)
   at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:560)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:102)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:656)
   at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:926)
   at java.lang.Thread.run(Thread.java:748)

Hello Ram,

The most common cause for this error is a TIMEOUT, what seems the case, as the code works in DEV and UAT, but not in production.

So, I would change the logic to check, before executing the query, if you are near the timeout period (that you can change in the timer properties), and awake again the timer, ending the current execution. Remember to mark anything you already processed as done to not create an infinite looping. 

There may be other causes, like errors in filters and so on, that could end in this error, what seems not to be the case, table locks and connection problems that you should investigate as well.

Cheers.

HI Eduado, 

Thanks for reply, Timer job timeout is set to 20 m, and i am getting the exception in 30-40 seconds. 


Hi Ram,

What Eduardo meant was that there was a timeout on the query request itself... from the OutSystems server to the Oracle server.

Yes,

Timeouts in the query means you have a connection problem, or another error that is cascading up to the timeout error, like a lock problem.

Cheers

Thanks Jorge, 

is there any solution to fix this?

Ram,

I suggest first you try and optimize your query?... only perform JOINs on relevant tables, reduce the number of records returned (using Max. Records, for instance), only SELECT the columns that are actually needed (do not use {Entity}.*), etc...

Alternatively, change the Default Query Timeout for the OutSystems database in the Configuration Tool, if it's an infrastructure issue and does not depend on what query you are executing.

Thanks Jorge, 

The query is already optimised, and i am using advanced query and selecting only required fields. 

User wants all the records as it is a report, and i didnt find the option for Default query timeout in the Outsystems version 10, 

Ram,

You can find documentation for OutSystems 10 here. You have the same setting, the tab just has a different name...