"Error: error - Service Unavailable" while running any job via oracle Apex Page

Question

I am executing a external job using DBMS_SCHEDULER through apex page by clicking a button in below manner.(Dynamic action=>Execute PlSql)

 dbms_scheduler.run_job(job_name => 'APEXDATA.myJobName', use_current_session=> TRUE);

Its executing the external job correctly.(taking 1-2 minutes).My issue is that, in between the time while its executing i can not able to access any other page or can not able to login with new session nothing.showing below error in every task i am performing.

**503 Service Unavailable
  The connection pool named: |apex|| is not correctly configured, due to the following error(s): 
  Exception occurred while getting connection: oracle.ucp.UniversalConnectionPoolException: 
  All connections in the Universal Connection Pool are in use**

Is this the general or known issue?if yes how to resolve the issue,because in same time other user also has to perform any other task or other may login same time.

Thank You.

Answer 1

I think you're mixing 2 things that hard to combine:

1. Dynamic actions are designed to submit code from the page without a page submit so the user can continue to work on the page after he has done something (eg run pl/sql code)
2. Running a process in the database that takes up the database session until it is completed ( use_current_session=> TRUE). Your dbms_scheduler.run_job process will run in the current session and as long as that job is running no other operations can be run in that database session (the connection is in use as shown in the error message).

Solutions:

• use_current_session=> FALSE so the job runs in the background
• In the dynamic action, set "Wait for result" to true, so the user is forced to wait until the job completes.
• Execute the job on page submit which will also force the user to wait for the job to be completed.

Since your job takes 1-2 mins to complete, options 2 and 3 are probably not feasible because the user experience is not optimal. If you execute the job in the background, then you probably need to write some additional code to prevent the user from clicking a couple of times and submitting the job multiple times. You could do that by checking if the job is running before you submit it and not submit it if it is currently running.