[bug]: ORA-20987: APEX - Your session has ended

[devrebeleza]

Flows for APEX version

22.2

Oracle Database version

19c

Oracle APEX version

22.2

Have you used the BPMN-Linter in Flow Designer to Validate your Diagram?

Yes

What happened?

We have three levels of workflows, they work independently and are related by data stored in tables.
Level three could have around 5000 open instances.

We create a JOB that, for a Level 2 instance, runs a loop and try to move forward many of these level three instances. But, at some point the execution stops with the error "ORA-20987: APEX - Your session has ended"

Complete error:
ERROR: ORA-20987: APEX - Your session has ended. - Contact your application administrator.,
backtrace: ORA-06512: at "APEX_220100.WWV_FLOW_ERROR", line 1073
ORA-06512: at "APEX_220100.WWV_FLOW_ERROR", line 1521
ORA-06512: at "APEX_220100.WWV_FLOW_SESSION", line 2088
ORA-06512: at "APEX_220100.WWV_FLOW_SESSION", line 2307
ORA-06512: at "APEX_220100.WWV_FLOW_SESSION_API", line 76
ORA-06512: at "FLOWS4APEX.FLOW_APEX_SESSION", line 293
ORA-06512: at "FLOWS4APEX.FLOW_API_PKG", line 242
ORA-06512: at "FLOWS4APEX.FSOD_UAC_PKG", line 312
ORA-06512: at "FLOWS4APEX.FSOD_UAC_PKG", line 371

--
FLOWS4APEX.FSOD_UAC_PKG is our custom package that executes, line 312, the following procedure

FLOW_API_PKG.FLOW_COMPLETE_STEP(
p_process_id => p_process,
p_subflow_id => p_subflow,
p_step_key => p_step
);

--
FLOWS4APEX.FLOW_API_PKG, line 242, is an execution of delete_session procedure in an exception block
exception
when others then
if l_session_id is not null then
line 242-> flow_apex_session.delete_session (p_session_id => l_session_id );
end if;
raise;

--
Workflow Level 3:

The last Activity (Notify Role Group instance of completion) runs only a log process.

Steps to reproduce

Create many instance of a workflow. Try to complete step of many of them using a JOB.

Expected behaviour

No session ended and all instances should move forward.

[devrebeleza]

Workaround:

Before calling FLOWS4APEX.FLOW_API_PKG.FLOW_COMPLETE_STEP I create a session for flows4apex using the function flows4apex.flow_apex_session.create_api_session, and set the maximum idle time for that session using APEX_UTIL.SET_SESSION_MAX_IDLE_SECONDS(p_seconds => g_max_idle_sec);

g_max_idle_sec is a global variable with a time in second to set.

maybe, FLOWS4APEX can have a session timeout setting and be used internally.