Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2012
    Posts
    7

    Unanswered: PL SQL help - beginner. Pretty sure its a simple mistake

    Hello, I am having issues with PL/SQL, I feel as though what I am doing is right. I have a student table with (Student.ID, Student.name, Student.Midterm, Student.Final, Student.Homework) and a weights table with (Weights.MidPercent, Weights.FinPercent, Weights.HWPercent) I cant print out Student.Name and Student.ID. I get the following error:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 35
    06502. 00000 - "PL/SQL: numeric or value error%s"

    my code is as follows:

    DROP TABLE STUDENT CASCADE CONSTRAINTS;
    CREATE TABLE STUDENT
    (
    ID CHAR(3),
    Name VARCHAR2(20),
    Midterm NUMBER(3,0) CHECK (Midterm>=0 AND Midterm<=100),
    Final NUMBER(3,0) CHECK (Final>=0 AND Final<=100),
    Homework NUMBER(3,0) CHECK (Homework>=0 AND Homework<=100),

    PRIMARY KEY (ID)
    );
    INSERT INTO STUDENT VALUES ( '111', 'aaad', 12, 32, 23 );
    INSERT INTO STUDENT VALUES ( '121', 'sdfsdfw', 44, 75, 83 );
    INSERT INTO STUDENT VALUES ( '123', 'bfde', 94, 39, 51 );
    INSERT INTO STUDENT VALUES ( '222', 'bab', 69, 51, 53 );
    INSERT INTO STUDENT VALUES ( '232', 'asf', 77, 85, 83 );
    INSERT INTO STUDENT VALUES ( '122', 'xxx', 57, 66, 40 );
    SELECT * FROM STUDENT;

    DROP TABLE WEIGHTS CASCADE CONSTRAINTS;
    CREATE TABLE WEIGHTS
    (
    MidPercent NUMBER(2,0) CHECK (MidPercent>=0 AND MidPercent<=100),
    FinPercent NUMBER(2,0) CHECK (FinPercent>=0 AND FinPercent<=100),
    HWPercent NUMBER(2,0) CHECK (HWPercent>=0 AND HWPercent<=100)
    );
    INSERT INTO WEIGHTS VALUES ( 20, 20, 80 );


    DECLARE
    midweight weights.midpercent%type;
    finalweight weights.finpercent%type;
    hmwkweight weights.hwpercent%type;
    x student.midterm%type;
    y student.final%type;
    z student.homework%type;
    sid Student.id%type;
    sname Student.name%type;
    letterGrade Char(1);
    weightsTitle varchar2(20) :='Weights are ';

    CURSOR studentweight IS SELECT student.id, student.name, student.Midterm*.01*midweight, student.Final*.01*finalweight, student.Homework*.01*hmwkweight FROM STUDENT;
    BEGIN

    SELECT midPercent, FinPercent, HWPercent INTO midweight, finalweight, hmwkweight FROM weights;
    dbms_output.put_line(weightsTitle || midweight || ', ' || finalweight || ', ' || hmwkweight);

    OPEN studentweight;
    LOOP
    FETCH studentweight INTO sid, sname, x, y, z;
    IF x+y+z >=90 THEN
    letterGrade := 'A';
    elsif x+y+z >= 80 THEN
    letterGrade := 'B';
    elsif x+y+z >= 70 THEN
    letterGrade := 'C';
    elsif x+y+z >= 0 THEN
    letterGrade := 'F';
    End if;
    IF studentweight%FOUND THEN
    dbms_output.put_line(sid || ' ' || sname || ' ' || x+y+z || ' ' || letterGrade);
    END IF;
    EXIT WHEN studentweight%NOTFOUND;
    END LOOP;
    CLOSE studentweight;
    END;
    /

    anyone be able to help me???
    Last edited by getmet; 11-04-12 at 16:45.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have a student table with
    That is nice but we don't have this table unless & until you provide
    CREATE TABLE STUDENTS statement for us.

    >ORA-06512: at line 35
    which is Line #35?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2012
    Posts
    7
    sorry here it is:

    DROP TABLE STUDENT CASCADE CONSTRAINTS;
    CREATE TABLE STUDENT
    (
    ID CHAR(3),
    Name VARCHAR2(20),
    Midterm NUMBER(3,0) CHECK (Midterm>=0 AND Midterm<=100),
    Final NUMBER(3,0) CHECK (Final>=0 AND Final<=100),
    Homework NUMBER(3,0) CHECK (Homework>=0 AND Homework<=100),

    PRIMARY KEY (ID)
    );
    INSERT INTO STUDENT VALUES ( '111', 'aaad', 12, 32, 23 );
    INSERT INTO STUDENT VALUES ( '121', 'sdfsdfw', 44, 75, 83 );
    INSERT INTO STUDENT VALUES ( '123', 'bfde', 94, 39, 51 );
    INSERT INTO STUDENT VALUES ( '222', 'bab', 69, 51, 53 );
    INSERT INTO STUDENT VALUES ( '232', 'asf', 77, 85, 83 );
    INSERT INTO STUDENT VALUES ( '122', 'xxx', 57, 66, 40 );
    SELECT * FROM STUDENT;

    DROP TABLE WEIGHTS CASCADE CONSTRAINTS;
    CREATE TABLE WEIGHTS
    (
    MidPercent NUMBER(2,0) CHECK (MidPercent>=0 AND MidPercent<=100),
    FinPercent NUMBER(2,0) CHECK (FinPercent>=0 AND FinPercent<=100),
    HWPercent NUMBER(2,0) CHECK (HWPercent>=0 AND HWPercent<=100)
    );
    INSERT INTO WEIGHTS VALUES ( 20, 20, 80 );

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    DBMS_OUTPUT.PUT_LINE expects a character string. In your case, X + Y + Z is a number so you need to convert it to a character. In order to do that, you'd use the TO_CHAR function:
    Code:
    dbms_output.put_line(sid || ' ' || sname || ' ' || to_char(x+y+z) || ' ' || letterGrade);

  5. #5
    Join Date
    Nov 2012
    Posts
    7
    UGH! OMG!! i knew it was something simple. i couldnt figure it out for the life of me. When i printed it out without the char values, x+y+z worked so i assumed it was due to the character strings. MAN! i wasted so much time on this

    THANK YOU SOO SOOOO MUCH!!!! very much appreciated.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    X + Y + Z, when you printed it alone, was implicitly converted to a character value. But, when there was a mix of characters and numbers, Oracle wanted you to explicitly do the job (so you used TO_CHAR).

  7. #7
    Join Date
    Nov 2012
    Posts
    7
    Quote Originally Posted by Littlefoot View Post
    X + Y + Z, when you printed it alone, was implicitly converted to a character value. But, when there was a mix of characters and numbers, Oracle wanted you to explicitly do the job (so you used TO_CHAR).
    Thank you again. That helps out even more.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •