Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2006
    Posts
    7

    Unanswered: Sql Procedure Error - Help Please

    can anyone please help me out..... .i have this coding, which is bringing me the error at the end, the line referred to is on bold..... I would really appreciate it if anyone can show me where i am going wrong thank you!!!



    CREATE OR REPLACE PROCEDURE GRADES
    AS

    v_student_id Result.student_id%type;
    v_marks Result.marks%type;
    V_module_name Module.module_name%type;
    v_year Result.year%type;
    v_first_name Module_Leader.First_name%type;
    v_Sur_name Module_Leader.Sur_Name%type;
    v_semester Result.Semester%type;
    v_grade varchar2(20);

    BEGIN



    DBMS_OUTPUT.PUT_LINE;

    LOOP

    SELECT Student.First_name, Student.Sur_name, Result.Year,
    Result.Semester, Module_leader.First_name
    FROM Student, Result, Module_leader;


    IF v_marks >= 70 THEN
    v_grade := 'DISTINCTION';


    ELSIF v_marks BETWEEN 50-69
    v_grade := 'PASS';


    ELSE v_marks <50
    v_grade := 'FAIL YOU NEED HELP!';



    END IF;

    END LOOP;

    END;

    /

    encountered the symbol "V_GRADE" when expecting one of the following:
    * & - + / at mod rem <an exponent (**)> and ||

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    BETWEEN 50 and 69
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2006
    Posts
    7
    Thanks for the solution, buit after resolving the problem, another problem came up, again the error at the bottom, and the line/coding its reffering to is in bold.


    CREATE OR REPLACE PROCEDURE GRADES
    AS

    v_student_id Result.student_id%type;
    v_marks Result.marks%type;
    V_module_name Module.module_name%type;
    v_year Result.year%type;
    v_first_name Module_Leader.First_name%type;
    v_Sur_name Module_Leader.Sur_Name%type;
    v_semester Result.Semester%type;
    v_grade varchar2(20);

    BEGIN

    LOOP

    DBMS_OUTPUT.PUT_LINE;

    SELECT Student.First_name, Student.Sur_name, Result.Year,
    Result.Semester, Module_leader.First_name
    FROM Student, Result, Module_leader;


    IF v_marks >= 70 THEN
    v_grade := 'DISTINCTION';


    ELSIF v_marks BETWEEN 50 AND 69 THEN
    v_grade := 'PASS';

    ELSE v_marks <50
    v_grade := 'FAIL YOU NEED HELP!';




    END IF;

    END LOOP;

    END;

    /

    encountered the symbol "<" when expecting one of the following:
    := . ( @ % ;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ELSE v_grade := 'FAIL YOU NEED HELP!';
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2006
    Posts
    7
    The errors are shown below highlighted




    SQL> CREATE OR REPLACE PROCEDURE GRADES
    2 AS
    3
    4 v_student_id Student.student_id%type;
    5 v_marks Result.marks%type;
    6 V_module_name Module.module_name%type;
    7 v_year Result.year%type;
    8 v_first_name Module_Leader.First_name%type;
    9 v_Sur_name Module_Leader.Sur_Name%type;
    10 v_semester Result.Semester%type;
    11 v_grade varchar2(20);
    12
    13 BEGIN
    14
    15 SELECT
    16
    17 LOOP
    18
    19 SELECT Student.First_name, Student.Sur_name, Result.Year,
    20 Result.Semester, Module_leader.First_name, Result.marks
    21 INTO v_first_name, v_sur_name, v_year, v_semester, v_first_name
    22 v_marks
    23 FROM Student, Result, Module_leader
    24 WHERE student_id='0402625';
    25
    26 END LOOP;
    27
    28 DBMS_OUTPUT.PUT_LINE;
    29
    30 IF v_marks >= 70 THEN
    31 v_grade := 'DISTINCTION';
    32
    33
    34 ELSIF v_marks BETWEEN 50 AND 69 THEN
    35 v_grade := 'PASS';
    36
    37
    38 ELSE v_grade := 'FAIL YOU NEED HELP!';
    39
    40
    41 END IF;
    42
    43 END;
    44
    45 /

    Warning: Procedure created with compilation errors.

    SQL>
    SQL>
    SQL>
    SQL>
    SQL> SHOW ERROR
    Errors for PROCEDURE GRADES:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    16/65530 PL/SQL: SQL Statement ignored
    19/2 PL/SQL: ORA-00923: FROM keyword not found where expected
    26/5 PLS-00113: END identifier 'LOOP' must match 'GRADES' at line 1,
    column 11

    28/1 PLS-00103: Encountered the symbol "DBMS_OUTPUT"
    SQL>

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    line 16 -- SELECT statement ignored

    also, i'm going to move this thread out of the MySQL forum and into the Oracle forum as it appears that you are not using MySQL

    good luck!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2006
    Posts
    7

    Help WITH MY WORK PLEASE....PROCEDURES

    I am trying to compile this procedure, with the errors coming up listed below....can anyone plaese help me resolve these errors please. Thank you....

    The Lines where the errors are are in bold.

    SQL> CREATE OR REPLACE PROCEDURE GRADES
    2 AS
    3
    4 v_student_id Student.student_id%type;
    5 v_marks Result.marks%type;
    6 V_module_name Module.module_name%type;
    7 v_year Result.year%type;
    8 v_first_name Module_Leader.First_name%type;
    9 v_Sur_name Module_Leader.Sur_Name%type;
    10 v_semester Result.Semester%type;
    11 v_grade varchar2(20);
    12
    13 BEGIN
    14
    15 SELECT
    16
    17 LOOP

    18
    19 SELECT Student.First_name, Student.Sur_name, Result.Year, 20 Result.Semester, Module_leader.First_name, Result.marks
    21 INTO v_first_name, v_sur_name, v_year, v_semester, v_first_name
    22 v_marks
    23 FROM Student, Result, Module_leader
    24 WHERE student_id='0402625';
    25
    26 END LOOP;
    27
    28 DBMS_OUTPUT.PUT_LINE;
    29
    30 IF v_marks >= 70 THEN
    31 v_grade := 'DISTINCTION';
    32
    33
    34 ELSIF v_marks BETWEEN 50 AND 69 THEN
    35 v_grade := 'PASS';
    36
    37
    38 ELSE v_grade := 'FAIL YOU NEED HELP!';
    39
    40
    41 END IF;
    42
    43 END;
    44
    45 /

    Warning: Procedure created with compilation errors.


    SQL> SHOW ERROR
    Errors for PROCEDURE GRADES:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    16/65530 PL/SQL: SQL Statement ignored
    19/2 PL/SQL: ORA-00923: FROM keyword not found where expected
    26/5 PLS-00113: END identifier 'LOOP' must match 'GRADES' at line 1,
    column 11

    28/1 PLS-00103: Encountered the symbol "DBMS_OUTPUT"
    SQL>

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The homework tutor is down the hall & 2nd door on the left.
    If you can not figure out your own syntax errors, maybe you should drop out of class ASAP & save yourself a failing grade & avoid expecting others to do YOUR work for you.
    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.

  9. #9
    Join Date
    Dec 2006
    Posts
    7
    Quote Originally Posted by anacedent
    The homework tutor is down the hall & 2nd door on the left.
    If you can not figure out your own syntax errors, maybe you should drop out of class ASAP & save yourself a failing grade & avoid expecting others to do YOUR work for you.
    i thought this was meant to be a place where i come for help...
    Last edited by r937; 12-08-06 at 15:31.

  10. #10
    Join Date
    Dec 2003
    Posts
    1,074
    1) The line
    Code:
    15 SELECT
    is just hanging there, it's use is not syntactically correct.

    2) I don't think your loop will ever end, as there is no condition stated which indicates when it should stop.

    3)
    Code:
    28 DBMS_OUTPUT.PUT_LINE;
    needs a string argument

    Start with that.
    -cf

  11. #11
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Although it is quite OK to come here and ask for help, errors you made show that you have no idea about what you are doing.

    This is a PL/SQL procedure, but you are unfamiliar with commands it supports. LOOP indicates that you'd like to ... well, loop through a certain data set. I guess that SELECT statement should, actually, be a cursor. I'd suggest you to use cursor FOR loop (instead of a simple infinite (as Chuck already told you) loop you use at the moment. Also, don't declare variables explicitly - use cursor variable instead.

    Furthermore, SELECT statement you wrote is - in my opinion - incorrectly written. Although it might compile and, actually, return some values, I'm quite sure you wouldn't be satisfied with it as you use 3 tables but only 1 WHERE condition. In other words, you miss something like
    Code:
    ... AND student.student_id = result.student_id
        AND module_leader.some_column = student/result.some_column
        AND perhaps something else
    I'd suggest you to run SELECT statement separately (in SQL*Plus), not as part of a PL/SQL procedure, make sure it is correctly written and that it returns required data set. THEN use it in the PL/SQL procedure.

    Moreover, as previously stated, you don't know how to properly use IF-THEN-ELSE structure, along with the DBMS_OUTPUT package.

    I believe that you should take some time, visit this Oracle Technology Network Documentation Library page and get familiar with the SQL Reference and PL/SQL User's Guide and Reference books. This site requires registration (which is free).

    Although it wouldn't be a problem to write a procedure which would compile and do something, it would help you shortly. But next week you'll get another, more complicated homework and you'll be in even bigger trouble. You can't expect to be spoonfed; learn it by yourself, and you'll probably know it. If we write your homework, it is us who should get a 'PASS' mark, not you.

    Finally: after you learn things I (and the others) told you, write this procedure from scratch. If you still have some questions, do come back and we'll be glad to help. Not spoonfeed.

Posting Permissions

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