[Timer Map] TimerMap 3.0.1 throwing an error in Oracle, "ORA-01843: not a valid month"
Forge component by António Dias
Platform Version
11.9.2 (Build 29615)

Hi folks,


Any idea why this error, thrown by an Aggregate? While testing in Service Studio it does not throw any error, but also outputs no results, either.


[1] Error executing query.
at ssTimerMap.Flows.FlowMainFlow_v3.ScrnHeatmap_TimerRuns.FuncssPreparation.datasetGetMostFrequentTimes(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, DateTime qpdaTimerRun_StartDate, Boolean qpboIncludeZeroDuration)
at ssTimerMap.Flows.FlowMainFlow_v3.ScrnHeatmap_TimerRuns.Preparation(HeContext heContext)
at ssTimerMap.Flows.FlowMainFlow_v3.ScrnHeatmap_TimerRuns.Page_Load(Object sender, EventArgs e)
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

[2] ORA-01843: not a valid month
at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at OutSystems.HubEdition.DatabaseProvider.Oracle.ExecutionService.ExecutionService.ExecuteReader(IDbCommand cmd)
at OutSystems.Internal.Db.DatabaseAccess.ExecuteWithoutRequestTimeout[T](Func`1 action)
at OutSystems.Internal.Db.ManagedCommand.ExecuteReader(String description, Boolean isApplication, Boolean transformParameters, Boolean skipLog)
at OutSystems.Internal.Db.DatabaseAccessProvider`1.ExecuteQuery[T](Command cmd, GenericRecordList`1 rl, String description, Boolean transformParameters, Boolean skipLog)
   at ssTimerMap.Flows.FlowMainFlow_v3.ScrnHeatmap_TimerRuns.FuncssPreparation.datasetGetMostFrequentTimes(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, DateTime qpdaTimerRun_StartDate, Boolean qpboIncludeZeroDuration)



Image69.png

Hey Pedro,

Can you check if changing from:


To a nulldate():


Solves the error?

Greetings,


When removing the DateTimeToDate outsystems function, it stops raising the exception, so it's an aggregate SQL build problem.



Best Regards,

Nelson Sousa

Hello again,


The tool isn't Oracle ready. The timer "AggregateTimerMetrics" also raises an exception due to a query in it:


OutSystems.HubEdition.RuntimePlatform.DataBaseException: Error executing query. ---> OutSystems.HubEdition.RuntimePlatform.DataBaseException: Error in advanced query CalculateTimerMetrics in Timer_AggregateTimerMetrics in TimerMap_CS (SELECT LogEntries.[Cyclic_Job_Key], LogEntries.[Espace_Id], COUNT(LogEntries.[Instant]), SUM(CASE WHEN (LogEntries.[Error_Id] LIKE '') THEN 0 ELSE 1 END), AVG(CAST(LogEntries.[Duration] AS DECIMAL)), MIN(LogEntries.[Duration]), MAX(LogEntries.[Duration]) FROM ( SELECT {Log_Cyclic_Job}.* FROM {Log_Cyclic_Job} WHERE {Log_Cyclic_Job}.[Instant] >= @IntervalStart and {Log_Cyclic_Job}.[Instant] < @IntervalEnd UNION SELECT {Log_Cyclic_Job_Previous}.* FROM {Log_Cyclic_Job_Previous} WHERE {Log_Cyclic_Job_Previous}.[Instant] >= @IntervalStart and {Log_Cyclic_Job_Previous}.[Instant] < @IntervalEnd ) LogEntries GROUP BY LogEntries.[Espace_Id], LogEntries.[Cyclic_Job_Key] ): ORA-00904: "LOGENTRIES"."Cyclic_Job_Key": invalid identifier
--- End of inner exception stack trace ---
at ssTimerMap_CS.Actions.FuncActionTimer_AggregateTimerMetrics.QueryCalculateTimerMetrics(HeContext heContext, Int32 maxRecords, IterationMultiplicity multiplicity, Int64& outParamCount, DateTime qpdaIntervalStart, DateTime qpdaIntervalEnd)
at ssTimerMap_CS.Actions.ActionTimer_AggregateTimerMetrics(HeContext heContext)
   at ssTimerMap_CS.TimerAggregateTimerMetrics.Execute(HeContext heContext, Int32 timeout)


Best Regards,

Nelson Sousa

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.