Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

COUNT(*) causes NO_DATA_FOUND exception

48387Feb 26 2002
The output of this trigger is the following:
BEFORE SELECT102END
***SHOULD NOT GET HERE!***
Declare
*
ERROR at line 1:
ORA-20921: STUDENT CANNOT ACCESS THE COURSE
ORA-06512: at "LOAD_ACTIVITY", line 218
ORA-06512: at "LOAD_ACTIVITY", line 261
ORA-06512: at line 19

Here is the code...HOW CAN COUNT(*) CAUSE NO_DATA_FOUND???

CREATE OR REPLACE TRIGGER check_student_grp
BEFORE INSERT
ON course_activity FOR EACH ROW
DECLARE
v_vDPECError dpec_errors_table.error_description%TYPE;
v_nDPECErrorCode dpec_errors_table.error_code%TYPE;
v_vDPECAction dpec_errors_table.error_action%TYPE;
v_count NUMBER;
BEGIN
dbms_output.put_line('BEFORE SELECT'||:new.stuid||'END');
SELECT count(*)
INTO v_count
FROM student_grps s,hub h
WHERE s.stuid = :new.stuid
AND h.gid = s.gid;
dbms_output.put_line('AFTER SELECT'||:new.stuid||'END');
IF v_count = 0 then
RAISE dpec_errors.STUDENT_GRP_INVALID;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('***SHOULD NOT GET HERE!***');
RAISE;
WHEN dpec_errors.STUDENT_GRP_INVALID THEN
dpec_errors.get_error('STUDENT_GRP_INVALID',v_nDPECErrorCode,v_vDPECError,v_vDPECAction);
RAISE_APPLICATION_ERROR(v_nDPECErrorCode,v_vDPECError);
END;
/

ANY IDEAS???

TIA,
RICH
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 26 2002
Added on Feb 26 2002
7 comments
1,690 views