CODEWITHSUNDEEP
oracle-databaseplsql
cay cospete
cay cospete

Reputation: 33

PLS-00382: expression is of wrong type in Cursor

DECLARE

A_NAME STUDENTS_1.NAME%TYPE;
B_NAME STUDENTS_1.NAME%TYPE;
C_NAME STUDENTS_1.NAME%TYPE;
GRADE_ST STUDENTS_1.GRADE%TYPE;


CURSOR A IS SELECT NAME FROM STUDENTS_1 WHERE REGEXP_LIKE(GRADE,'(A)','i');
CURSOR B IS SELECT NAME FROM STUDENTS_1 WHERE REGEXP_LIKE(GRADE,'(B)','i');
CURSOR C IS SELECT NAME FROM STUDENTS_1 WHERE REGEXP_LIKE(GRADE,'(C)','i');


BEGIN
GRADE_ST:= &CHOICE;


IF (GRADE_ST='A') THEN

OPEN A;
LOOP

FETCH A INTO A_NAME;

EXIT WHEN A%NOTFOUND;

INSERT INTO SCHOLARSHIP_A VALUES(A_NAME);

END LOOP;
CLOSE A;


ELSIF (GRADE_ST='B') THEN 

OPEN B;
LOOP

FETCH B INTO B_NAME;

EXIT WHEN B%NOTFOUND;

INSERT INTO SCHOLARSHIP_B VALUES(B_NAME);

END LOOP;
CLOSE B;


ELSE

OPEN C;
LOOP

FETCH C INTO C_NAME;

EXIT WHEN C%NOTFOUND;

INSERT INTO SCHOLARSHIP_C VALUES(C_NAME);

END LOOP;

CLOSE C;



END IF;

END;

/

I'm making three different scholarship tables with grades A,B,C if i user input values rather than grades i'm getting the solution but i don't want to give values as user input i want user input should be grades like if i input A i should get all names with grade A from the table but i'm ending up with this error

Upvotes: 1

Views: 1776

Answers (2)

Alex Poole
Alex Poole

Reputation: 191255

SQL*Plus or SQL Developer is substituting the choice variable value before the PL/SQL block is parsed, and as @mathguy suggested that may not ultimately be what you want. But here if you had set verify on you would able to see the code that is actually being compiled, and would see that is causing this error; this part:

BEGIN
GRADE_ST:= &CHOICE;

ends up actually being, if the user supplies the value A:

BEGIN
GRADE_ST:= A;

and in this scope A is the cursor name. So it's an invalid assignment, of a cursor to a string; hence the error you get.

Because your choice is a char/string you need to enclose it in quotes, so you should be doing:

BEGIN
GRADE_ST:= '&CHOICE';

You could greatly simplify what you are doing, and don't actually need PL/SQL, but that is beyond the scope of your question.

Upvotes: 3

user5683823
user5683823

Reputation:

You are using a substitution variable (&choice) to pass in the argument. This is a SQL*Plus feature; you will need to pass in the value before your code is compiled, and then it is hard-coded in the block. Rather, what you need is a bind variable (:choice), or write this as a procedure that takes an in parameter.

Upvotes: 2

Related Questions