We are using APEX 22.1.5, ORDS- 21.2, DB- Oracle Database 21c Standard Edition, Application Server Tomcat 9, Server is OCI.
We have installed JasperReportsIntegration latest version and created a report.
Requirement: Report is build for individual employees, we need to send the report through REST API as base64 format. We are taking all employees in loop and generating Jasper URL, and accessing URL using UTL_HTTP.BEGIN_REQUEST and convert it as BLOB. After getting BLOB, converting it as BASE64 format and send through REST API.
While loop is executing and accessing Jasper URL, JDBC connection is opened for executing SQL queries in that Jasper report. After getting report the connection becomes inactive. So inactive sessions are gradually increased in DB and exceeds the process limit of 400.
We are getting this error in DB log,
ORA-00020: maximum number of processes (400) exceeded
ORA-20 errors will not be written to the alert log for
the next minute. Please look at trace files to see all
the ORA-20 errors.
it leads to internal server error inside the code and failed to send report.
How can we close the JDBC connection from code or using any configuration in order to avoid inactive sessions.
Please help us in this issue.
Thanks & Regards,