How to define OUT parameter without input in pl/sql?

October 12, 2022

I’m asked to save both min and max GPAs into 2 OUT maxStudentGPA and minStudentGPA. I can do this without the OUT restriction like this:

create or replace procedure MinMaxGPA
(
    p_CLASSNAME in class.classname%type
)
as
    maxStudentGPA student.gpa%type;
    minStudentGPA student.gpa%type;
begin
    select max(gpa) into maxStudentGPA
    from student
    where classno = (select classno from class where upper(classname) = upper(p_CLASSNAME));
    dbms_output.put_line(maxStudentGPA);
    
    select min(gpa) into minStudentGPA
    from student
    where classno = (select classno from class where upper(classname) = upper(p_CLASSNAME));
    dbms_output.put_line(minStudentGPA);
end MinMaxGPA;

But if I add OUT to them like this:

    maxStudentGPA out student.gpa%type;
    minStudentGPA out student.gpa%type;

It throw me an error message like this:

6/23      PLS-00103: Encountered the symbol "STUDENT" when expecting one of the following:     := . ( @ % ; not null range default character The symbol ":=" was substituted for "STUDENT" to continue. 
7/23      PLS-00103: Encountered the symbol "STUDENT" when expecting one of the following:     := . ( @ % ; not null range default character The symbol ":=" was substituted for "STUDENT" to continue.

Can you explain to me why won’t it work?

>Solution :

It doesn’t work because you are trying to declare an OUT parameter in the declaration section of the procedure while that can only be done in the parameter_declaration section of the procedure. Check the docs for more details.

create or replace procedure MinMaxGPA
(
    -- *parameter declaration* 
    -- here you define the arguments for your procedure
    -- arguments can be IN (default), OUT or IN OUT
    p_CLASSNAME in class.classname%type
)
as
    -- *declare section*
    -- here you define variables that you will use in the procedure body
    -- they have nothing to do with IN or OUT
    maxStudentGPA student.gpa%type;
    minStudentGPA student.gpa%type;
...
<rest_of_code>

So I think what you’re trying to do is:

create or replace procedure MinMaxGPA
(
    p_CLASSNAME in class.classname%type,
    p_maxStudentGPA OUT student.gpa%type,
    p_minStudentGPA OUT student.gpa%type

)
as
    maxStudentGPA student.gpa%type;
    minStudentGPA student.gpa%type;
begin
    select max(gpa) into maxStudentGPA
    from student
    where classno = (select classno from class where upper(classname) = upper(p_CLASSNAME));
    dbms_output.put_line(maxStudentGPA);
    
    select min(gpa) into minStudentGPA
    from student
    where classno = (select classno from class where upper(classname) = upper(p_CLASSNAME));
    dbms_output.put_line(minStudentGPA);
    -- assign the calculated variables to the out parameters.
    p_maxStudentGPA := maxStudentGPA;
    p_minStudentGPA := minStudentGPA;
end MinMaxGPA;
/