Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: forms session cursors do not close.
"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.
It should if you are explicitly falling through there.
Are you sure you're closing them *and* it's falling through that section of code? (Forgive me though if I'm stating the obvious if you've already done this.)
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);
MESSAGE('You entered an invalid value, please try again); PAUSE; RAISE FORM_TRIGGER_FAILURE; CLOSE C1; ELSE CLOSE C1;
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);
CLOSE C1; MESSAGE('You entered an invalid value, please try again); PAUSE; RAISE FORM_TRIGGER_FAILURE; ELSE CLOSE C1;
DECLARE CURSOR C1 IS
SELECT 'X' FROM SOME_TABLE WHERE SOME_COLUMN = :MYBLOCK.MYITEM; D_DUMMY VARCHAR2(1) := NULL;
MESSAGE('You entered an invalid value, please try again); PAUSE; RAISE FORM_TRIGGER_FAILURE;
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 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;
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 CDT