Re: forms session cursors do not close.

From: Matt B. <mcb_at_fightspam.sd.znet.com>
Date: 2000/05/22
Message-ID: <sijsmjd5o127_at_corp.supernews.com>#1/1


[Quoted] "Zekun Zou" <z.zou_at_paradise.net.nz> wrote in message news:HjlW4.16163$usz4.10420568_at_news.xtra.co.nz...
> Hi Oracle forms experts,
>
> Can you help me?
>
> My problem is that:
> One of my oracle form4.5 applications opens too many cursors and do not
> close them.
> The error message is:
> ORA-01000 maximum open cursors exceeded.
>
> In the forms, for all query statment, I use CLOSE cursorName explicity, but
> it do not work.

[Quoted] It should if you are explicitly falling through there.

Are you sure you're closing them *and* it's falling through that section of [Quoted] code? (Forgive me though if I'm stating the obvious if you've already done this.)

[Quoted] Example (assume on POST-CHANGE that we are validating something the user entered against a validation table):

DECLARE CURSOR C1 IS

        SELECT 'X'
        FROM SOME_TABLE
        WHERE SOME_COLUMN = :MYBLOCK.MYITEM;
        D_DUMMY CHAR(1);

BEGIN
   OPEN C1;
   FETCH C1 INTO D_DUMMY;
   IF C1%NOTFOUND THEN
      MESSAGE('You entered an invalid value, please try again); PAUSE;
      RAISE FORM_TRIGGER_FAILURE;
      CLOSE C1;
   ELSE
      CLOSE C1;

   END IF;
END; In the above example, FORM_TRIGGER_FAILURE would prevent the CLOSE C1 from executing because it essentially stops processing and breaks out of the procedure or trigger.

You'd need to do it like this:

DECLARE CURSOR C1 IS

        SELECT 'X'
        FROM SOME_TABLE
        WHERE SOME_COLUMN = :MYBLOCK.MYITEM;
        D_DUMMY CHAR(1);

BEGIN
   OPEN C1;
   FETCH C1 INTO D_DUMMY;
   IF C1%NOTFOUND THEN
      CLOSE C1;
      MESSAGE('You entered an invalid value, please try again); PAUSE;
      RAISE FORM_TRIGGER_FAILURE;
   ELSE
      CLOSE C1;

   END IF;
END; Or like this:

DECLARE CURSOR C1 IS

        SELECT 'X'
        FROM SOME_TABLE
        WHERE SOME_COLUMN = :MYBLOCK.MYITEM;
        D_DUMMY VARCHAR2(1) := NULL;

BEGIN
   OPEN C1;
   FETCH C1 INTO D_DUMMY;
   CLOSE C1;
   IF D_DUMMY = 'X' THEN
      MESSAGE('You entered an invalid value, please try again); PAUSE;
      RAISE FORM_TRIGGER_FAILURE;

   END IF;
END; Or like this:

DECLARE D_DUMMY CHAR(1) := NULL;
BEGIN
   SELECT 'X'
   INTO D_DUMMY
   FROM SOME_TABLE
   WHERE SOME_COLUMN = :MYBLOCK.MYITEM;
EXCEPTION
   WHEN NO_DATA_FOUND THEN

      MESSAGE('You entered an invalid value, please try again); PAUSE;
      RAISE FORM_TRIGGER_FAILURE;
   WHEN TOO_MANY_ROWS THEN
      MESSAGE('More than one validation record was found'); PAUSE;  --Assuming
[Quoted] this is a case that can happen and that's what you want to do
      RAISE FORM_TRIGGER_FAILURE;
   WHEN OTHERS
      MESSAGE('An unknown error has occurred; please contact your system
administrator'); PAUSE;
      RAISE FORM_TRIGGER_FAILURE;

END; There are probably a few other flavors to the above samples too. Anyway, check [Quoted] to make sure that you are closing all cursors and that processing will "flow" [Quoted] thorough to your close statement.

Also, I think that if you call other procedures and *those* procedures (attached libraries, DB procedures, etc.) fail to close cursors, you still might have the problem.

-Matt Received on Mon May 22 2000 - 00:00:00 CEST

Original text of this message