Re: forms session cursors do not close.

Thanks for your message.

My PL/SQL code in forms is like this:
It is in the trigger pre-insert:
DECLARE
  CURSOR C1 IS
    SELECT SYSDATE
    FROM DUAL;
BEGIN
  OPEN C1;
  FETCH C1 INTO :B_BLOCK1.DATEINSERT;
  CLOSE C1;
END; I use the Oracle view: V$OPEN_CURSOR to monitor my form application session and I find that the cursor C1 keeps open.

SQL>select sid, sql_text from v$open_cursor where sid=12; (where 12 is forms session id)

And idea?

• Quinn

Matt B. <mcb_at_fightspam.sd.znet.com> wrote in message news:sijsmjd5o127_at_corp.supernews.com...
> "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);
> 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');
 AUSE; --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;
> END;
>
> There are probably a few other flavors to the above samples too. Anyway,
 check
> to make sure that you are closing all cursors and that processing will
 "flow"
> 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 Tue May 23 2000 - 00:00:00 CEST