Your browser was unable to load all of the resources. They may have been blocked by your firewall, proxy or browser configuration.
Press Ctrl+F5 or Ctrl+Shift+R to have your browser try again.

daust / JasperReportsIntegration

JasperReportsIntegration provides an interface to use the JasperReports reporting engine in an Oracle database application, specifically with Oracle Application Express (Oracle APEX).

help wanted

JDBC Connection is becomes Inactive and Occupied the processes & Exceeds 400 limit #67

lakshmis2022 ·
#1

Hi,

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,
Lakshmi

  • replies 1
  • views 796
  • likes 0
Dietmar Aust daust · Team
#2

Hi @lakshmis2022,

do you still have this issue?

Not exactly sure, how I can help here. I am using Oracle JDBC driver (ojdbc8.jar) and Universal Connection Pool (ucp.jar) for the connection pooling with this code:

Greenshot 2023-09-14 12.22.21.png

Thus, the connection pool is limited to 50 sessions (per JasperReportsIntegration instance).
Do you have multiple instances of JRI running?

Are you sure, that these additional sessions are created by JRI?

Inactive sessions typically are not an issue. Can you simply increase the number of sessions? Or will it just buy time and hit the new limit a bit later?

Best,
~Dietmar.