CODEWITHSUNDEEP
oracle-databaseplsql
user3127434
user3127434

Reputation: 15

Unexpected error in PL/SQL case statement

I'm new in PL/SQL try to make case for grade but it's showing identifier error

SET serveroutput ON;
SET verify OFF
DECLARE
  v_grade CHAR(1) := UPPER(&GPA);
  appre   VARCHAR2(20);
BEGIN
  appre := CASE v_grade
  WHEN 'A' THEN 'Excellent'
  WHEN 'B' THEN 'Very Good'
  WHEN 'C' THEN 'Good'
  ELSE 'No Such Grade'
  END;
  DBMS_OUTPUT.PUT_LINE( 'Grade is '|| v_grade || ' is equal to ' || appre );
END;
/

This is error i got

Error report -
ORA-06550: line 2, column 28:
PLS-00201: identifier 'E' must be declared
ORA-06550: line 2, column 11:
PL/SQL: Item ignored

Upvotes: 0

Views: 331

Answers (2)

pifor
pifor

Reputation: 7892

This works with only 1 modification for '&GPA'

SET serveroutput ON;
SET verify OFF
DECLARE
  v_grade CHAR(1) := UPPER('&GPA');
  appre   VARCHAR2(20);
BEGIN
 appre := CASE v_grade
  WHEN 'A' THEN 'Excellent'
  WHEN 'B' THEN 'Very Good'
  WHEN 'C' THEN 'Good'
  ELSE 'No Such Grade'
  END;
 DBMS_OUTPUT.PUT_LINE( 'Grade is '|| v_grade || ' is equal to ' || appre );
END;
/

This also works with the other CASE syntax:

SET serveroutput ON;
SET verify OFF
DECLARE
  v_grade CHAR(1) := UPPER('&GPA');
  appre   VARCHAR2(20);
BEGIN
 CASE 
  WHEN v_grade = 'A' THEN appre:='Excellent';
  WHEN v_grade = 'B' THEN appre:='Very Good';
  WHEN v_grade = 'C' THEN appre:='Good';
  ELSE appre := 'No Such Grade';
 END CASE;
 DBMS_OUTPUT.PUT_LINE( 'Grade is '|| v_grade || ' is equal to ' || appre );
END;
/

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132640

Your issue is with this line:

v_grade CHAR(1) := UPPER(&GPA);

At run time you are prompted for a value for GPA. If you type in just the letter E it resolves to:

v_grade CHAR(1) := UPPER(E);

That requires an identifier e.g. variable called E. You need it to resolve to a string:

v_grade CHAR(1) := UPPER('E');

So change it to this:

v_grade CHAR(1) := UPPER('&GPA');

Upvotes: 3

Related Questions