java.sql.SQLExc eption: ORA-00020: maximum number of processes (100) exceeded

[sumithradevi]

Hello Friends,

I am getting the following error.
java.sql.SQLExc eption: ORA-00020: maximum number of processes (100)
exceeded

I am closing all my resultsets and all my connections in the try
block.
should i close ResultSets and Connections in the catch block aswell?.

or the problem is due to the connection pool settings in weblogic or
on the oracle side instead of in my application.

Thanks in Advance
s

[Nathan Zumwalt]

You need to close at least your Connection object in the finally
clause, like this:

Connection conn = null;
try {
//get connection...

//do stuff...

} catch (SQLException sqle) {
e.printStackTra ce();
} finally {
try { conn.close(); } catch (Exception e) { //do nothing }
}

If your application hits the database a lot, you might want to have
your DBA increase the maximum number of processes (I'm not a DBA, so I
don't know how to do this, or what side-effects it might have).

-Nathan

su**********@ho tmail.com wrote in message news:<2e******* *************** ***@posting.goo gle.com>...

Hello Friends,

I am getting the following error.
java.sql.SQLExc eption: ORA-00020: maximum number of processes (100)
exceeded

I am closing all my resultsets and all my connections in the try
block.
should i close ResultSets and Connections in the catch block aswell?.

or the problem is due to the connection pool settings in weblogic or
on the oracle side instead of in my application.

Thanks in Advance
s

[PerfectDayToChaseTornados]

<su**********@h otmail.com> wrote in message
news:2e******** *************** **@posting.goog le.com...
| Hello Friends,
|
| I am getting the following error.
| java.sql.SQLExc eption: ORA-00020: maximum number of processes (100)
| exceeded
|
| I am closing all my resultsets and all my connections in the try
| block.
| should i close ResultSets and Connections in the catch block aswell?.
|
| or the problem is due to the connection pool settings in weblogic or
| on the oracle side instead of in my application.
|
| Thanks in Advance
| s

You should try to close them in a finally block (checking first to see that
the connection is not null) this way you will be sure that they get closed.
How many multiple connections do you get on your server? If it is a busy
website you may well need in excess of 100 connections in the pool. We have
max connections set to 200 in ours. A good way to close your connections so
that you don't have to have too many nested connections is as follows:

try{
Connection con = myDataSource.ge tConnection();
try{
// database call code here
}
finally{
if(con != null){
con.close();
}
}
}
catch(SQLExcept ion sqle){
//handle exception here
}
--
-P

[PerfectDayToChaseTornados]

OOps 'nested connections' should say nested try catch finally blocks :-)
Long day at work ;-)
--
-P

[Raymond DeCampo]

PerfectDayToCha seTornados wrote:

You should try to close them in a finally block (checking first to see that
the connection is not null) this way you will be sure that they get closed.
How many multiple connections do you get on your server? If it is a busy
website you may well need in excess of 100 connections in the pool. We have
max connections set to 200 in ours. A good way to close your connections so
that you don't have to have too many nested connections is as follows:

try{
Connection con = myDataSource.ge tConnection();
try{
// database call code here
}
finally{
if(con != null){
con.close();
}
}
}
catch(SQLExcept ion sqle){
//handle exception here
}

Hmmm; don't you risk the possibility that an exception on the
con.close() will overshadow another exception and make debugging more
difficult?

I usually use the following pattern:

Connection conn = null;
try
{
conn = getConnection() ;
// use connection
}
catch (final SQLException sqle)
{
// handle exception; call conn.rollback() if desired
}
finally
{
try
{
if (conn != null)
{
conn.close();
}
}
catch (final SQLException sqle)
{
// log exception
}
}

The nesting is no deeper than the original code.

Actually, this kind of issue makes me wish that Java supported the
notion of attaching a finally block to a method. The syntax would look like:

public void doStuff()
{
// do stuff
}
finally
{
// clean up
}

That would make a lot of code look cleaner.

Ray