DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: number precision too large error in procedure

  1. #1
    Join Date
    Sep 2002
    Posts
    1

    Exclamation

    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;


  2. #2
    Join Date
    Feb 2001
    Posts
    180
    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
  •  


Click Here to Expand Forum to Full Width