Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: beginner Help me!
Hi
The obvious problem here is that you are
not opening your cursor before fetching
from it.
Another potential problem is that when
you raise form_trigger_failure you are going
to leave the cursor hanging open.
A suggestion - instead of
SELECT count(*) ..............
why don't you create another cursor
and do a single fetch. Obviously 1 or
more is valid so you really only need
to know that there is at least 1 record
and it will be far more efficent.
Regards
John
Tel: (702) 498 4990
Fax: (702) 871 4318
e-mail: jomarlen_at_aol.com
Web Site: http://members.aol.com/jomarlen/
The views expressed in this message
are those of the author and not
necessarily those of U.P. Inc.
and/or its employees.
>hello
>
>I am currently working with a small school project that is focussed on
>tracking a students performance in the school. I came across this
>problem. Like, one of the requirement is to allow a student to register
>for a term. Meaning, the student will enter 3 courses that he wants to
>register for the term. I must immedietely check his records and see if
>he met the pre requisites for those courses he wants to register. Since,
>a course can have more than one prerequisties, I had to use a cursor to
>fetch all the prerequistes for each course and check his database to see
>if he had already done those courses. If not I will not allow him to
>take that course and advice him accordingly. This is the code I wrote
>and I repeatedly get invalid cursor error. Can someone point out the bug
>in this.
>
>****
>Code written in Key next item trigger
>
>DECLARE
> VAR1 NUMBER;
> VAR2 NUMBER;
> PR1 COURSE.COURSE_NUM%TYPE;
> BEGIN
> DECLARE
> CURSOR CRCUR IS
> SELECT PRE_REQNUM FROM COURSE
> WHERE COURSE_NUM = :REGISTER.COURSE_NUM;
> BEGIN
> LOOP
> FETCH CRCUR INTO VAR1;
> /* THE USER ENTERS THE COURSE NUMBER AND HIS SOCIAL SEC NUM TO
>REGISTER */
> SELECT COUNT(*) INTO VAR2 FROM COURSE_TAKEN
> WHERE COURSE_NUM = VAR1 AND SSN = :REGISTER.SSN;
> IF VAR2 = 0 THEN
> MESSAGE('Pre Requisites not met for this course');
> MESSAGE('Pre Requisites not met for this course');
> RAISE FORM_TRIGGER_FAILURE;
> END IF;
> EXIT WHEN CRCUR%NOTFOUND;
> END LOOP;
> CLOSE CRCUR;
> END;
>END;
>
>
>Thanks in advance
>
>Please email me at vks7584_at_tntech.edu
>
>regards
>
>
>vinod
>
>
>
>
>
>
>
Received on Fri Oct 31 1997 - 00:00:00 CST