From oracle-l-bounce@freelists.org Tue Jun 8 05:00:05 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i589xoG19049 for ; Tue, 8 Jun 2004 05:00:00 -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 i589xe619022 for ; Tue, 8 Jun 2004 04:59:50 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2D9E672C1AF; Tue, 8 Jun 2004 04:45:29 -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 01825-08; Tue, 8 Jun 2004 04:45:29 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8214A72C1DC; Tue, 8 Jun 2004 04:45:28 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 08 Jun 2004 04:44:09 -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 C317372C0BD for ; Tue, 8 Jun 2004 04:44:08 -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 00950-33 for ; Tue, 8 Jun 2004 04:44:08 -0500 (EST) Received: from mail014.syd.optusnet.com.au (mail014.syd.optusnet.com.au [211.29.132.160]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 084BF72C09A for ; Tue, 8 Jun 2004 04:44:05 -0500 (EST) Received: from [192.168.0.155] (c211-30-91-186.belrs3.nsw.optusnet.com.au [211.30.91.186]) (authenticated) by mail014.syd.optusnet.com.au (8.11.6p2/8.11.6) with ESMTP id i58A3C707107 for ; Tue, 8 Jun 2004 20:03:12 +1000 Message-ID: <40C58EE2.8020801@optusnet.com.au> Date: Tue, 08 Jun 2004 20:03:14 +1000 From: Nuno Souto Organization: Nunovyabizzness User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: oracle-l@freelists.org Subject: Re: Cats, Pigeons and Open Cursors References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2227 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: dbvision@optusnet.com.au Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Piet de Visser allegedly said,on my timestamp of 8/06/2004 7:13 PM: > 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 ? Well, there are probably a zillion solutions. The one I have used quite successfully is as follows: 1- in the package declaration, declare a type of REF CURSOR. Like this: type zot_the_cursor is REF CURSOR; and declare all your functions to return this type. As in: function blah (par1 in number) return zot_the_cursor; 2- In the package body, for every function start it with: IF zot_the_cursor%ISOPEN then close zot_the_cursor; END IF; just like you suggested. It worked for me. Release 8.1.7.4. -- Cheers Nuno Souto in sunny Sydney, Australia dbvision@optusnet.com.au ---------------------------------------------------------------- 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 -----------------------------------------------------------------