Re: forms session cursors do not close.
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