From oracle-l-bounce@freelists.org Tue Jun 8 14:03:30 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i58J3Fh17403 for ; Tue, 8 Jun 2004 14:03:25 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i58J32617361 for ; Tue, 8 Jun 2004 14:03:12 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7EFFE72CCEE; Tue, 8 Jun 2004 13:48:00 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26799-10; Tue, 8 Jun 2004 13:48:00 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1290D72CE02; Tue, 8 Jun 2004 13:47:02 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 08 Jun 2004 13:45:16 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9B62E72CC71 for ; Tue, 8 Jun 2004 13:45:13 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 26054-44 for ; Tue, 8 Jun 2004 13:45:13 -0500 (EST) Received: from ftp.bluelineresults.com (unknown [216.222.6.52]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4FDCB72C6E9 for ; Tue, 8 Jun 2004 13:44:57 -0500 (EST) Received: (qmail 31393 invoked by uid 0); 8 Jun 2004 19:01:26 -0000 Received: from justin@askddbc.com by em by uid 11186 with qmail-scanner-1.20 (clamscan: 0.60. Clear:RC:1(192.168.5.15):. Processed in 0.770729 secs); 08 Jun 2004 19:01:26 -0000 Received: from unknown (HELO terminal) ([192.168.5.15]) (envelope-sender ) by 0 (qmail-ldap-1.03) with SMTP for ; 8 Jun 2004 19:01:22 -0000 From: "Justin Cave" To: Subject: RE: Cats, Pigeons and Open Cursors Date: Tue, 8 Jun 2004 13:02:01 -0600 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 Thread-Index: AcRNXtZyY3epBUppTxiwNXSMUn/yNgALC42w In-Reply-To: <000001c44d5e$d6720e00$0a05a8c0@ddbc.local> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.132 X-Qmail-Scanner-Message-ID: <108672128665431381@em> Message-Id: <20040608184458.4FDCB72C6E9@turing.freelists.org> X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2276 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: justin@askddbc.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org is something I fancy myself to be. I probably should have further qualified the statement to say that it is relatively easy to ensure that cursors are closed in virtually every case. Handling every possible run-time exception is probably tough to do, but hopefully you don't regularly run out of memory in the JVM. I would argue that it is a heck of lot easier with OO languages to handle errors than it was in the old procedural days. Of course, just like those of us whose mothers made us clean our rooms, those of us that learned how to do things like free our own memory have some advantages over developers that were trained that the language would collect all their garbage. With languages like Java, you can almost always end up with something like try { << open cursor >> << process results >> << close cursor >> } exception(SQLException e ) { <> } Which is a lot easier to deal with than trying to remember to check 15 different return values to check for an error code. Sure, there are some persistence frameworks that abstract all this away, but those are generally well written to do this sort of thing internally. Your application may have to indicate to the framework that you are done with the results of the query, but at that point you've basically got the same problem as above of making sure you have to appropriate exception handler in place. I guess my point is, if the Java side of the house is complaining that they can't possibly be expected to close all the cursors in a custom application, they are either - Making up excuses to get out of work - Not particularly well trained in enterprise Java development - Using alpha frameworks that they found on some random web page, using frameworks they wrote themselves, poorly, or using frameworks that they haven't taken the time to learn properly - In need of some education about resource management, and enterprise databases - Operating under the belief that there is a magical "garbage collect" call that you can make in the database that will fix the problem rather than having to look through source code for bugs. Justin Cave Distributed Database Consulting, Inc. http://www.ddbcinc.com/askDDBC -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of DENNIS WILLIAMS Sent: Tuesday, June 08, 2004 7:45 AM To: oracle-l@freelists.org Subject: RE: Cats, Pigeons and Open Cursors Justin I agree with you 99%. My only quibble is with your statement "relatively easy to walk through the code". I won't pass myself off as a Java expert, but just want to point out that OOP like Java have a much more complex execution path than the older procedural languages most of us are familiar with. Transaction boundaries become very difficult to delineate, especially with event-driven GUI interfaces. Then there are things like J2EE that can handle database access automagically. Part of the value of modern development environments like Java is to utilize code others have written rather than writing every line of code yourself like most of us did in the older languages. I'm not denying that it may be necessary in some situations for the execution path to be traced in detail to determine the problem, just that the "relatively easy" may not be quite the case. Dennis Williams DBA Lifetouch, Inc. dwilliams@lifetouch.com -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Justin Cave Sent: Tuesday, June 08, 2004 4:57 AM To: oracle-l@freelists.org Subject: RE: Cats, Pigeons and Open Cursors I think you are running into a fundamental problem. Once you return a cursor to the Java application, the Java application owns it. Oracle doesn't generally allow you to come along and kill things owned by another session. The only ways I'm aware of to close the cursor would be to do so in the Java application or to kill the entire session from PL/SQL. If your Java developers are moderately competent, it should be relatively easy to walk through the code and ensure that all the cursors are closed by the appropriate exception block. If there are a few corner cases where cursors are not closed, you can cycle connections from the connection pool or close long-open connections from PL/SQL. The former option will probably cause your Java application fewer problems. Justin Cave Distributed Database Consulting, Inc. http://www.ddbcinc.com/askDDBC -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Piet de Visser Sent: Tuesday, June 08, 2004 3:31 AM To: oracle-l@freelists.org Subject: Cats, Pigeons and Open Cursors List, We have a nice problem between Java and Pl/sql, and since PL/sql is slightly out of my comfort zone, I'm submitting it to the list (fairly sure we are not the first to stumble on this). A procedure is used to open an number of ref-cursors and pass them back to java as out-parameters. Which cursors are opened varies and depends on in-parameters. When Java is done using the cursors, it Should close all the opened cursors. Sometimes the closing is skipped or forgotten, and the nr-open-cursors increases rapidly. Since connections are rarely closed, but rather re-used, the db runs out of open cursors. Given the fact that we do not quite trust the Java code to always close all cursors, we would like to build a sure-fire way in Pl/sql to close all previously opened cursors. First suggestion was to create another procedure that checks all cursors using ISOPEN%cursor123 and closes them. This doesn't work because REF-cursors can apparently not be declared outside of functions or procedures. Question: - Is there a way to refer to ref-cursors inside a package, and to have a list if ref-cursors closed conditionally by using : IF refcur1%ISOPEN THEN CLOSE refcur1 ; END IF; ? More precisely, we want the package to keep track of all cursors it has opened (we can keep a list, no problem if that requires some extra code). We then want the same procedure or another procedure to go in, read the list of open cursors, an close any that are still open. Is this feasible ? Suggestions anyone ? Regards, PdV -- Tribute to All quotes on Developers, Cats, -- -- Statues and Pigeons. It is all True. -- -- Disclaimers are like Art -- -- You can read in them whatever you want -- This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------