Re: tomcat issue with DBCP connection pool
Are you sure it is due to the stored procedure? You can get that error if you don't close all database objects. Make sure you close resultsets, statements, etc in a "finally" block throughout all your code. On Mon, Mar 21, 2011 at 4:59 PM, Propes, Barry L wrote: > And to thisis it absolutely necessary to use the SYS_REFCURSOR in Oracle? > I use several packages and procesures and never use this! Granted, I may have > many less records than you in your DB table, but I was just wondering. > Perhaps it is. > > >> BTW, my application calls a stored procedure which in turn uses an >> oracle SYS_REFCURSOR. As I know ref cursor can not be closed through code. > > How are you supposed to close that cursor, then? Did you mean you can't close > it through Java code, or you can't even close it through PL/SQL? > >> Once the error is encountered the application is not recovering until >> the application is restarted. > > Sounds like closing the connection and re-initializing it would probably do > the job, too. Is it possible for you to detect the kinds of requests that > will allocate these SYS_REFCURSORs and use a one-time connection instead of > going through the pool? > > - -chris > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.10 (MingW32) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ > > iEYEARECAAYFAk2HYRkACgkQ9CaO5/Lv0PAOpQCfWY5OKJoRo1C8Ld//bOTpCC3x > t/QAn2qdGxo0uXDU01rf6A+IElsrLDTq > =40df > -END PGP SIGNATURE- > > - > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > > > - > To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org > For additional commands, e-mail: users-h...@tomcat.apache.org > > - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
RE: tomcat issue with DBCP connection pool
And to thisis it absolutely necessary to use the SYS_REFCURSOR in Oracle? I use several packages and procesures and never use this! Granted, I may have many less records than you in your DB table, but I was just wondering. Perhaps it is. > BTW, my application calls a stored procedure which in turn uses an > oracle SYS_REFCURSOR. As I know ref cursor can not be closed through code. How are you supposed to close that cursor, then? Did you mean you can't close it through Java code, or you can't even close it through PL/SQL? > Once the error is encountered the application is not recovering until > the application is restarted. Sounds like closing the connection and re-initializing it would probably do the job, too. Is it possible for you to detect the kinds of requests that will allocate these SYS_REFCURSORs and use a one-time connection instead of going through the pool? - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2HYRkACgkQ9CaO5/Lv0PAOpQCfWY5OKJoRo1C8Ld//bOTpCC3x t/QAn2qdGxo0uXDU01rf6A+IElsrLDTq =40df -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
Re: tomcat issue with DBCP connection pool
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 To whom it may concern, On 3/21/2011 6:59 AM, the_biswa wrote: > I've written a thread program where in the run method I'm opening an > HTTPURLConnection and feeding a valid input and reading the server response. Do you launch this thread during normal request processing? If so, do you wait on this thread to complete before allowing the request processing thread to continue? I'm asking because if you launch a thread and then complete the request without waiting, there is a high likelihood that you will inappropriately re-use request-scoped objects and create havoc with the container. If you are waiting on the secondary thread anyway, then it's not really necessary at all. > Running the program for as many as 600 threads with no delay inbetween > thread-calls I can get all the 600 requests processed without any issue. > > However, when I run the wget command (wget --post-file=ac.xml > --header=Content-Type:text/xml > http://10.227.30.204:9090/ABCService/ABCService) in a loop of 300 iteration > to load-test the application I see that the application is failing after > request 297 with the following error > > java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1 > ORA-01000: maximum open cursors exceeded Sounds like one of two problems: 1. You are not properly closing your database resources when you are finished with them (see http://blog.christopherschultz.net/index.php/2009/03/16/properly-handling-pooled-jdbc-connections/) 2. You need more database resources allocated on the server (see Oracle documentation for configuring server-side resources) > BTW, my application calls a stored procedure which in turn uses an oracle > SYS_REFCURSOR. As I know ref cursor can not be closed through code. How are you supposed to close that cursor, then? Did you mean you can't close it through Java code, or you can't even close it through PL/SQL? > Once the error is encountered the application is not recovering until the > application is restarted. Sounds like closing the connection and re-initializing it would probably do the job, too. Is it possible for you to detect the kinds of requests that will allocate these SYS_REFCURSORs and use a one-time connection instead of going through the pool? - -chris -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAk2HYRkACgkQ9CaO5/Lv0PAOpQCfWY5OKJoRo1C8Ld//bOTpCC3x t/QAn2qdGxo0uXDU01rf6A+IElsrLDTq =40df -END PGP SIGNATURE- - To unsubscribe, e-mail: users-unsubscr...@tomcat.apache.org For additional commands, e-mail: users-h...@tomcat.apache.org
