-
Hey everyone, I'm new. Thought I may get some help from this forum. I am in advanced dba programming (oracle 8) BUt my teacher does not use a book. I am a getting this error message and don't know where it is. I'm suppose to create a stored proceedure but can't even get the proceedure to work so I can move on. Basically I need to calculate the gpa for a group of students and I have to use a open, get fetch. The error message is:
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 50
*** The line number may have changes sinc I changed the code. The problem is with this line:
v_final_gpa := v_total_num / v_classCtr; --calculates final gpa
****
here is the code... any help would be appreciated:
DECLARE
v_sid students.student_id%type;
v_total_num number(3) := 0;
v_classCtr NUMBER(2) :=0; --counts # of classes for gpa calculation
v_num_grade NUMBER(2) :=0; --i.e. a=4, b=3
v_name students.name%type;
v_hold_id students.student_id%type:= 0; --temp placeholder for sid
v_grade grades.grade%type;
v_final_gpa number(1,2) := 0; --calculated gpa to print out
--v_current_student number(1,0) := 1; --1 = true, 0 = fales
--v_1st_run number(1,0) := 1;
CURSOR get_student_data IS
SELECT students.student_id, students.name, grades.grade
FROM students, grades
WHERE students.student_id = grades.student_id
ORDER by students.student_id;
BEGIN
OPEN get_student_data;
LOOP
FETCH get_student_data INTO v_sid, v_name, v_grade; -- putting student info into cursor
--while there are students in the table
IF get_student_data%found THEN
IF v_hold_id <> v_sid then
v_total_num :=0;
v_classCtr :=0;
v_final_gpa :=0;
v_hold_id := v_sid; -- indicates new student
END IF;
--determines number value for letter grade
IF v_grade = 'A' then v_num_grade := 4;
DBMS_output.put_line('v_grade is A added ' || v_grade ||' to v_num_grade v_num_grade is now ' || v_num_grade );
elsif v_grade = 'B' then v_num_grade := 3;
DBMS_output.put_line('v_grade is B added ' || v_grade ||' to v_num_grade v_num_grade is now ' || v_num_grade );
elsif v_grade = 'C' then v_num_grade := 2;
DBMS_output.put_line('v_grade is C added ' || v_grade ||' to v_num_grade v_num_grade is now ' || v_num_grade );
elsif v_grade = 'D' then v_num_grade := 1;
DBMS_output.put_line('v_grade is D added ' || v_grade ||' to v_num_grade v_num_grade is now ' || v_num_grade );
elsif v_grade = 'F' then v_num_grade := 0;
DBMS_output.put_line('v_grade is F added ' || v_grade ||' to v_num_grade v_num_grade is now ' || v_num_grade );
else
DBMS_output.put_line('Pleaseinput a grade between A and F');
-- ends all test for number value
end if;
v_total_num := v_total_num + v_num_grade;
DBMS_output.put_line('added v_gpa to v_num_grade, v_final_gpa is now ' || v_final_gpa);
v_classCtr := v_classCtr + 1; --counts number of classes for calculation
DBMS_output.put_line('classCtr is now ' || v_classCtr );
v_final_gpa := v_total_num / v_classCtr; --calculates final gpa
DBMS_output.put_line('v_sid has changed to ' || v_sid );
DBMS_output.put_line(v_hold_id || ' has a GPA of ' || v_final_gpa);--keep this print statement
END IF;
END LOOP;
CLOSE get_student_Data;
END;
-
The declaration:
v_final_gpa number(1,2) := 0;
Is not valid, the first number should always be bigger then the second, so at least:
v_final_gpa number(3,2) := 0;
Regards
Ben de Boer
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|