How can I solve PL/SQL: Statement ignored error?

Question

I'm trying to write a procedure. The procedure will be queried with the school number and the course_name, midterm_not, final_not, the average will be revealed, but the midterm and final grades will be calculated in %. If it is under 60, it will be over and it will be passed.

set serveroutput on;
CREATE OR REPLACE PROCEDURE student_grade(
        p_school_no IN lessons.school_number%type,
        p_lesson OUT lessons.lesson_name%type,
        p_midterm_1 OUT lessons.midterm_notu_1%type,
        p_midterm_2  OUT lessons.midterm_notu_2%type,
        p_final OUT lessons.final_notu%type,
        p_average OUT NUMBER
    )
    IS
    BEGIN
    SELECT
    d.lesson,
    d.midterm_notu_1,
    d.midterm_notu_2,
    d.final_notu
    INTO
        p_lesson,
        p_midterm_1,
        p_midterm_2,
        p_final
    FROM lessons d
    WHERE d.shool_number = p_school_no
    p_average := (((d.midterm_notu_1 * 25)/100) + ((d.midterm_notu_2 * 30)/100) + ((d.final_notu * 45)/100));
    END;
   
    DECLARE
    v_school_no lessons.school_number%type := 20201754;
    v_lesson lessons.lesson_name%type;
    v_midterm_1 lessons.midterm_notu_1%type;
    v_midterm_2  lessons.midterm_notu_2%type;
    v_final lessons.final_notu%type;
    v_average NUMBER;
    BEGIN
    student_grade(  
        v_lesson,
        v_midterm_1,
        v_midterm_2  ,
        v_final,
        v_average );
    DBMS_OUTPUT.put_line ('Student Grade');
    DBMS_OUTPUT.put_line ('School Number: ' ||v_school_no);
    DBMS_OUTPUT.put_line ('Midterm 1: ' || v_midterm_1);
    DBMS_OUTPUT.put_line ('Midterm 2: ' || v_midterm_2  );
    DBMS_OUTPUT.put_line ('Final: ' || v_final);
    DBMS_OUTPUT.put_line ('Average: ' || v_average );
    END;

I did something like this when I run the procedure it says "Procedure student_grade compiled" but when I try to run the DECLARE part it gives an error like this;

Error report -
ORA-06550: line 9, column 5:
PLS-00905: SYSTEM.STUDENT_GRADE object is invalid
ORA-06550: line 9, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

I think it has a problem in p_average := Can anyone help me with these issues?

Answer 1

You are almost done everything fine. Few things with respect to procedure,

Run the below command (may be you over look the part for error)

ALTER PROCEDURE student_grade COMPILE;
Warning: Procedure altered with compilation errors

---

you do not need to make the p_average parameter as IN OUT , OUT should be enough as you calculate it inside.

to assign some value to OUT parameter you don't need to use SET. Only assignment using assignment operator is fine. see below

CREATE OR REPLACE PROCEDURE student_grade
(
   p_school_no IN lessons.school_number%TYPE
  ,p_lesson    OUT lessons.lesson_name%TYPE
  ,p_midterm_1 OUT lessons.midterm_notu_1%TYPE
  ,p_midterm_2 OUT lessons.midterm_notu_2%TYPE
  ,p_final     OUT lessons.final_notu%TYPE
  ,p_average   OUT NUMBER
) IS
BEGIN
   SELECT d.lesson
         ,d.midterm_notu_1
         ,d.midterm_notu_2
         ,d.final_notu
   INTO   p_lesson
         ,p_midterm_1
         ,p_midterm_2
         ,p_final
   FROM   lessons d
   WHERE  d.shool_number = p_school_no;
   --assign to the output variable for average
   p_average := (((d.midterm_notu_1 * 25) / 100) + ((d.midterm_notu_2 * 30) / 100) + ((d.final_notu * 45) / 100));
END;
/

---

I believe because of error exists in procedure you were unable to test, after doing above changes it should work.

You also can test it with a PL/SQL anonymous block instead with SQL command window which would be easier. E.g.

DECLARE
    --assign the input directly here in the declare section
    v_school_no lessons.school_number%type := 10;
    v_lesson lessons.lesson_name%type;
    v_midterm_1 lessons.midterm_notu_1%type;
    v_midterm_2  lessons.midterm_notu_2%type;
    v_final lessons.final_notu%type;
    v_average NUMBER;
BEGIN
    -- call the procedure
    student_grade(  
        v_lesson,
        v_midterm_1,
        v_midterm_2  ,
        v_final,
        v_average );
    DBMS_OUTPUT.put_line ('Student Grade');
    DBMS_OUTPUT.put_line ('School Number: ' ||v_school_no);
    DBMS_OUTPUT.put_line ('Midterm 1: ' || v_midterm_1);
    DBMS_OUTPUT.put_line ('Midterm 2: ' || v_midterm_2  );
    DBMS_OUTPUT.put_line ('Final: ' || v_final);
    DBMS_OUTPUT.put_line ('Average: ' || v_average );
END;
/

Let me know if it solves your problem;

---

REVISED ANSWER WITH SPECIFIC PROBLEM WITH THE USE AND TOOL USAGE

I strongly recommend/suggests you to look into the documentation of PL/SQL before continue with next assignment. It will help you understanding the errors you get so you can correct it. Also there are many videos on the tool SQL Developer , how to use them efficiently.Check them too.

Coming back to you problem I have tried in my machine and there are many problems to the script which I had to fix one by one looking into the error messages. Please find the points and final solution which should work or else I am done.

Problems: there are differences in names of the column in table with the code you have written

1. WHERE d.shool_number = p_school_no; --> shool_number is not an existing column, it is propbably d.school_number
2. v_lesson lessons.lesson_name%type; --> the actual column is lesson and not lesson_name. I can say it from your select clause in the procedure
3. p_lesson OUT lessons.lesson_name%type, --> same as point 2
4. p_average := (((d.midterm_notu_1 * 25)/100) + ((d.midterm_notu_2 * 30)/100) + ((d.final_notu * 45)/100)); -- you cannot refer the columns like this , what it means with "d." where code doesn't know what it refers. d you have used in the select query as alias to table lessons and with the select statement ends the scope of d finishes there itself. As you have already taken the values to the output variables such as p_midterm_1, p_midterm_2, p_final use them instead.
5. Additionally make sure the select statement returns only one rows per school_number=20201754 otherwise you end of with error ORA-01422: exact fetch returns more than requested number of rows and then there are other ways to handle it. (for the moment I will not say anything with respect to that)
6. Final point where you try to test the procedure like student_grade( v_lesson, v_midterm_1, v_midterm_2 , v_final, v_average ); --> you are passing wrong number of arguments to the procedure by not including v_school_no as the first parameter.

However i have created my own setup and modified the procedure and the test accordingly, see below.

--table definition
create table lessons (school_number number,lesson varchar2(100),midterm_notu_1 number,midterm_notu_2 number,final_notu number);
--inserting unique rows per school_number
insert into lessons values(20201754,'Maths',35,55,85);
insert into lessons values(20201755,'Science',45,65,95);

-- to enable the dbms_output
SET SERVEROUTPUT ON;

--procedure definition
CREATE OR REPLACE PROCEDURE student_grade(
        p_school_no IN lessons.school_number%type,
        p_lesson OUT lessons.lesson%type,
        p_midterm_1 OUT lessons.midterm_notu_1%type,
        p_midterm_2  OUT lessons.midterm_notu_2%type,
        p_final OUT lessons.final_notu%type,
        p_average OUT NUMBER
    )
IS
BEGIN
    SELECT
    d.lesson,
    d.midterm_notu_1,
    d.midterm_notu_2,
    d.final_notu
    INTO
        p_lesson,
        p_midterm_1,
        p_midterm_2,
        p_final
    FROM lessons d
    WHERE d.school_number = p_school_no;
    p_average := (((p_midterm_1 * 25)/100) + ((p_midterm_2 * 30)/100) + ((p_final * 45)/100));
END student_grade;
/

--testing the procedure    
DECLARE
    v_school_no lessons.school_number%type := 20201754;
    v_lesson lessons.lesson%type;
    v_midterm_1 lessons.midterm_notu_1%type;
    v_midterm_2  lessons.midterm_notu_2%type;
    v_final lessons.final_notu%type;
    v_average NUMBER;
BEGIN
    student_grade(
        v_school_no,
        v_lesson,
        v_midterm_1,
        v_midterm_2  ,
        v_final,
        v_average );
    DBMS_OUTPUT.put_line ('Student Grade');
    DBMS_OUTPUT.put_line ('School Number: ' ||v_school_no);
    DBMS_OUTPUT.put_line ('Midterm 1: ' || v_midterm_1);
    DBMS_OUTPUT.put_line ('Midterm 2: ' || v_midterm_2  );
    DBMS_OUTPUT.put_line ('Final: ' || v_final);
    DBMS_OUTPUT.put_line ('Average: ' || v_average );
END;
/

Answer 2

The 'accept' (accept p_school_no prompt ) is a sqlplus directive, not a pl/sql statement. PL/SQL runs entirely inside the database and has no means of 'accepting' input from the user. The only means of 'accepting' run-time values is by supplying on the command line when you call the procedure. That's what those IN parameters are for.

exec student_grade('schoolname');

Also, 'set' (SET p_average := ) is not valid for a SELECT. It belongs with UPDATE.