ora-01795 when opening an App - leftover open activities
Confluence User - 07 Sep, 2022
Hi
we are on v5 and some users have started to experience an error just after a successful login and when opening their App:
Looking at the logs it appears that there is an SQL using the "IN" operator on a list longer than 1,000 elements (which is the max for ORacle DB. Hence the error :
ORA-01795: maximum number of expressions in a list is 1000
By looking at the culprit SQL statement I found that this is the query being performed that returns over 1000 elements which are than passed to the next SQL query:
SELECT sharkassig0_.oid AS oid1_2_0_, sharkproce1_.Id AS Id1_3_1_, sharkactiv2_.Id AS Id1_0_2_, sharkactiv3_.oid AS oid1_1_3_, sharkassig0_.ResourceId AS ResourceId2_2_0_, sharkassig0_.ActivityId AS ActivityId3_2_0_, sharkassig0_.ActivityProcessId AS ActivityProcessId4_2_0_, sharkproce1_.Name AS Name2_3_1_, sharkproce1_.ActivityRequesterProcessId AS ActivityRequesterP3_3_1_, sharkproce1_.version AS version4_3_1_, sharkproce1_.PDefName AS PDefName5_3_1_, sharkactiv2_.Name AS Name2_0_2_, sharkactiv2_.ActivityDefinitionId AS ActivityDefinition3_0_2_, sharkactiv2_.Activated AS Activated4_0_2_, sharkactiv2_.State AS State5_0_2_, sharkactiv3_.Name AS Name2_1_3_ FROM SHKAssignmentsTable sharkassig0_ INNER JOIN SHKProcesses sharkproce1_ ON sharkassig0_.ActivityProcessId = sharkproce1_.Id INNER JOIN SHKActivities sharkactiv2_ ON sharkassig0_.ActivityId = sharkactiv2_.Id INNER JOIN SHKActivityStates sharkactiv3_ ON sharkactiv2_.State = sharkactiv3_.oid WHERE 1 = 1 AND (sharkproce1_.PDefName LIKE 'blah_call_log_triage#%') AND sharkassig0_.ResourceId = 'USER01' AND (sharkactiv3_.Name LIKE 'open.%') order by sharkactiv2_.activated, sharkproce1_.created ;
which returned 1,001 rows for that particular user.
I suspect these rows feed the following query which has the "IN" operator:
SELECT COUNT (*)
FROM ( SELECT DISTINCT
APP_FD_JOG_CLT_CALL_LOG.ID
AS ID,
C_CALL_LOG_REF_ID,
APP_FD_JOG_CLT_CALL_LOG.C_STATUS,
APP_FD_JOG_CLT_CALL_LOG.C_CALL_LOGGED_BY,
APP_FD_JOG_CLT_CALL_LOG.C_CALL_LOGGED_ON,
APP_FD_JOG_CLT_REPORTABLE.C_REPORT_DETAIL_TYPE_REPORT,
APP_FD_JOG_CLT_REPORTABLE.C_REPORT_NOMINATE,
APP_FD_JOG_CLT_CALL_LOG.C_INITIAL_INFORMATION,
APP_FD_JOG_CLT_CALL_LOG.C_CALL_LOG_ATTACHMENTS,
APP_FD_JOG_CLT_REPORTABLE.C_REPORT_DETAILS_MATTER,
APP_FD_JOG_CLT_ADDRESS.C_ADDRESS_STREET_NAME,
APP_FD_JOG_CLT_ADDRESS.C_ADDRESS_SUBURB,
JOGET_USER_CODE_VIEW_PRIORITY.DESCRIPTION
AS TRIAGE_PRIORITY,
APP_FD_JOG_CLT_REPORTABLE.C_REPORT_PRIORITY,
JOGET_TEAM_SHORT_VIEW.TEAM
AS TRIAGE_TEAM,
APP_FD_JOG_CLT_REPORTABLE.c_report_response_escalated,
LISTAGG (C_PCBU_NAME, ', ')
WITHIN GROUP (ORDER BY C_PCBU_NAME)
"C_PCBU_NAME"
FROM APP_FD_JOG_CLT_CALL_LOG,
APP_FD_JOG_CLT_REPORTABLE,
APP_FD_JOG_CLT_ADDRESS,
APP_FD_JOG_CLT_PCBU_ITEM,
APP_FD_JOG_CLT_TRIAGE,
JOGET_USER_CODE_VIEW JOGET_USER_CODE_VIEW_PRIORITY,
JOGET_TEAM_SHORT_VIEW
WHERE APP_FD_JOG_CLT_CALL_LOG.ID = APP_FD_JOG_CLT_REPORTABLE.ID
AND APP_FD_JOG_CLT_REPORTABLE.C_DETAILS_ADDRESS_ID =
APP_FD_JOG_CLT_ADDRESS.ID(+)
AND APP_FD_JOG_CLT_REPORTABLE.ID =
APP_FD_JOG_CLT_PCBU_ITEM.C_PCBU_ITEM_ID(+)
AND APP_FD_JOG_CLT_REPORTABLE.ID = APP_FD_JOG_CLT_TRIAGE.ID(+)
AND APP_FD_JOG_CLT_TRIAGE.C_INFONET_PRIORITY =
JOGET_USER_CODE_VIEW_PRIORITY.CODE_LOV_ID(+)
AND APP_FD_JOG_CLT_TRIAGE.C_ASSIGN_CASE_FILE_TO_TEAM_ID =
JOGET_TEAM_SHORT_VIEW.TEAM_ID(+)
AND ( APP_FD_JOG_CLT_CALL_LOG.c_status = 'In Progress'
OR APP_FD_JOG_CLT_CALL_LOG.c_status = 'Pending')
GROUP BY APP_FD_JOG_CLT_CALL_LOG.ID,
C_CALL_LOG_REF_ID,
APP_FD_JOG_CLT_CALL_LOG.C_STATUS,
APP_FD_JOG_CLT_CALL_LOG.C_CALL_LOGGED_BY,
APP_FD_JOG_CLT_CALL_LOG.C_CALL_LOGGED_ON,
APP_FD_JOG_CLT_REPORTABLE.C_REPORT_DETAIL_TYPE_REPORT,
APP_FD_JOG_CLT_REPORTABLE.C_REPORT_NOMINATE,
APP_FD_JOG_CLT_CALL_LOG.C_INITIAL_INFORMATION,
APP_FD_JOG_CLT_CALL_LOG.C_CALL_LOG_ATTACHMENTS,
APP_FD_JOG_CLT_REPORTABLE.C_REPORT_DETAILS_MATTER,
APP_FD_JOG_CLT_ADDRESS.C_ADDRESS_STREET_NAME,
APP_FD_JOG_CLT_ADDRESS.C_ADDRESS_SUBURB,
JOGET_USER_CODE_VIEW_PRIORITY.DESCRIPTION,
APP_FD_JOG_CLT_REPORTABLE.C_REPORT_PRIORITY,
JOGET_TEAM_SHORT_VIEW.TEAM,
APP_FD_JOG_CLT_REPORTABLE.c_report_response_escalated
ORDER BY APP_FD_JOG_CLT_CALL_LOG.C_CALL_LOG_REF_ID DESC) temp
WHERE temp.ID IN ('197226_blah_call_log_triage_process5',
'205494_blah_call_log_triage_process5',
'193847_blah_call_log_triage_process5',
'',
'',
'',
....... a few more "''" for a total of 1,001
)
This user should not have these many SHKActivities records in a state of "open", but it does because several processes have all the dependant activities "closed" and only one "open". These are retrieved when populating the counters at the beginning and that causes the Oracle error.
We have to manually close the open activities to get to a number below 1,000.
And now the question:
Why do all these activities remain in an open state when the process is not even retrievable in a search?
Cheers
SR
ora-01795;ora-1795;list