I'm trying to create a procedure in Oracle. I want to be able to call on the procedure with a variable input and have a statement read. I am in the process of learning Oracle SQL, so I'm not sure where I'm going wrong.

This is my code:

create or replace procedure gradeCheck(grade char(1))

Begin 
  Case
    When grade = 'A' then DBMS_OUTPUT.PUT_LINE('Great Work')
    else DBMS_OUTPUT.PUT_LINE('Not great work')
  end case;
end gradeCheck;
/

Begin 
  gradeCheck(A);
end;
/

I want the input to be 'A' (the grade assignment) and to get the text output. I am getting a message that the GRADECHECK procedure is being compiled. But it won't let me call it with an input.

CodePudding user response：

• You want use IF not CASE
• You are missing the IS keyword before BEGIN
• You need to terminate statements with ;
• The CHAR data type does not have a length in the signature.

Like this:

create procedure gradeCheck(
  grade CHAR
)
IS
BEGIN
  IF grade = 'A' THEN
    DBMS_OUTPUT.PUT_LINE('Great Work');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Not great work');
  END IF;
END gradeCheck;
/

Then you need to use quotes around the string:

Begin
  gradeCheck('A');
end;
/

db<>fiddle here

CodePudding user response：

Your issues

• You are missing a word is in the create or replace statement
• If the input variable is string ( char ) then you have to enclose in quotes the value when you call the procedure.
• Use varchar2 instead of char, although it is not mandatory.
• ; must be at the end of dbms_output
• If you run it with sqlplus, use set serveroutput on to enable the output, that you can see the message.

Then

create or replace procedure gradeCheck(grade in varchar2)
is
Begin 
  case when grade = 'A' 
   then 
      DBMS_OUTPUT.PUT_LINE('Great Work');
   else 
      DBMS_OUTPUT.PUT_LINE('Not great work');
  end case;
end gradeCheck;
/

set serveroutput on 
Begin 
  gradeCheck('A');
end;
/

Test Case

SQL> l
  1   create or replace procedure gradeCheck(grade in varchar2)
  2   is
  3   Begin
  4     case
  5       when grade = 'A' then
  6         DBMS_OUTPUT.PUT_LINE('Great Work');
  7       else
  8         DBMS_OUTPUT.PUT_LINE('Not great work');
  9     end case;
 10*  end gradeCheck;
SQL> /

Procedure created.

SQL> begin
  2  gradeCheck('A');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> r
  1  begin
  2  gradeCheck('A');
  3* end;
Great Work

PL/SQL procedure successfully completed.