cursor not being compiled successfully in oracle

Question

I have table city which has two columns city_id and city_name.

So,I declared plsql block to see the output of this table.Here,what I tried is:

set serveroutput on
DECLARE 
    CURSOR CUR_NAME_LIST IS
    SELECT * FROM OT.CITY;

    V_NAME VARCHAR2(20);

BEGIN
    OPEN CUR_NAME_LIST;
    LOOP
    FETCH CUR_NAME_LIST INTO V_NAME;
    DBMS_OUTPUT.PUT_LINE(V_NAME.CITY_ID);
    EXIT WHEN CUR_NAME_LIST%NOTFOUND;
    END LOOP;
    CLOSE CUR_NAME_LIST;

END;
/

When I compiled,then I got the error as:

ORA-06550: line 11, column 33:
PLS-00487: Invalid reference to variable 'V_NAME'
ORA-06550: line 11, column 5:
PL/SQL: Statement ignored

Answer 1

Your cursor returns two columns so in INTO clause there must be two variable or one variable of type that has two columns.

In your case, define one more variable for id and use it in INTO clause. Or use variable of type city%rowtype to handle all the data.

Something like following:

set serveroutput on
DECLARE 
    CURSOR CUR_NAME_LIST IS
    SELECT * FROM OT.CITY;

    V_CITY CITY%ROWTYPE;

BEGIN
    OPEN CUR_NAME_LIST;
    LOOP
    FETCH CUR_NAME_LIST INTO V_CITY;
    DBMS_OUTPUT.PUT_LINE(V_CITY.ID || ' - ' || V_CITY.NAME );
    EXIT WHEN CUR_NAME_LIST%NOTFOUND;
    END LOOP;
    CLOSE CUR_NAME_LIST;

END;
/

-OR-

set serveroutput on
DECLARE 
    CURSOR CUR_NAME_LIST IS
    SELECT ID, NAME FROM OT.CITY;

    V_NAME VARCHAR2(20);
    V_ID NUMBER;

BEGIN
    OPEN CUR_NAME_LIST;
    LOOP
    FETCH CUR_NAME_LIST INTO V_ID, V_NAME;
    DBMS_OUTPUT.PUT_LINE(V_ID || ' - ' || V_NAME );
    EXIT WHEN CUR_NAME_LIST%NOTFOUND;
    END LOOP;
    CLOSE CUR_NAME_LIST;

END;
/

Cheers!!