Using Substitution variable in PL/SQL Code

Question

I am using SQL Developer and writing this PL/SQL code. But I am getting error, when i gave B as option. Please help.

SET SERVEROUTPUT ON

accept q_grade char prompt 'EnterGrade: '
DECLARE
v_grade CHAR(1) := UPPER(&q_grade);
v_appraisal VARCHAR(20);

BEGIN
v_appraisal := CASE v_grade WHEN v_grade = 'A' THEN 'Excellent'
WHEN v_grade = 'B' THEN 'Good'
WHEN v_grade = 'C' THEN 'FAIL'
ELSE 'NO SUCH GRADES'
END;

DBMS_OUTPUT.PUT_LINE ('Grade:  ' || v_grade  || ' Appraisal  ' ||v_appraisal);

END;
/

Error report:

ORA-06550: line 2, column 26:
PLS-00201: identifier 'B' must be declared
ORA-06550: line 2, column 9:
PL/SQL: Item ignored
ORA-06550: line 6, column 21:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 6, column 1:
PL/SQL: Statement ignored
ORA-06550: line 12, column 57:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 12, column 21:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Put single quotes around &q_grade.

Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні

2013-07-11 18:22:45 +00:00
Commented Jul 11, 2013 at 18:22 — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jul 11, 2013 at 18:22

Alejandro Colorado · Accepted Answer · 2013-07-11 18:33:56Z

2

Change:

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

and

v_appraisal := CASE WHEN v_grade = 'A' THEN 'Excellent'

answered Jul 11, 2013 at 18:33

Alejandro Colorado

6,0842 gold badges30 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

chue x · Accepted Answer · 2013-07-11 18:25:57Z

1

You need to remove the extra v_grade right after the CASE statement:

BEGIN
v_appraisal := CASE WHEN v_grade = 'A' THEN 'Excellent'
/* -- here --------^ */

answered Jul 11, 2013 at 18:25

chue x

18.9k8 gold badges60 silver badges71 bronze badges

1 Comment

Bob Jarvis - Слава Україні Over a year ago

@JeffreyKemp - whoooops! You're right - I shouldn't try to look at code at midnight. :-) Earlier comment deleted.

Collectives™ on Stack Overflow

Using Substitution variable in PL/SQL Code

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related