'PLS-00201: identifier 'USER INPUT' must be declared

I'm getting the error "PLS-00201: identifier 'CHICAGO' must be declared" when I try to enter the user input at the prompt "Please enter the Region:" If I enter "CHICAGO" for example(without quotes of course) I get the PLS-00201 error. I can't figure out why- any ideas? Thanks in advance.

ACCEPT p_1 PROMPT 'PLEASE ENTER THE REGION:'

DECLARE
  V_CHILD  REGIONS.CHILD_NAME%TYPE := &p_1;
  V_PARENT REGIONS.PARENT_NAME%TYPE;
  CURSOR REG_CUR (p_child_name varchar2) IS 
    SELECT UPPER(CHILD_NAME) 
      FROM REGIONS
     where CHILD_NAME = p_child_name;

BEGIN
  OPEN REG_CUR (V_CHILD);
  FETCH reg_cur INTO V_CHILD;
  WHILE REG_CUR%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(V_CHILD);
    FETCH reg_cur INTO V_CHILD;
  END LOOP;
  CLOSE REG_CUR;
END;
sqloracleplsqlsqlplusdatabase-cursor


Solution 1:[1]

If you're expecting a string, you need to enclose the substitution variable in quotes when you use it:

  V_CHILD  REGIONS.CHILD_NAME%TYPE := '&p_1';

As it is, it's trying to interpret the substituted value as an identifier, i.e. another variable or a column name. So if you entered CHICAGO it would see:

  V_CHILD  REGIONS.CHILD_NAME%TYPE := CHICAGO;

and if you entered "CHICAGO":

  V_CHILD  REGIONS.CHILD_NAME%TYPE := "CHICAGO";

In Oracle using double quotes still indicates an identifier. You could in fact have entered 'CHICAGO', with quotes in your input, and it would have worked - but not ideal to have to remember to do that.

If you set verify on in SQL*Plus or SQL Developer then you can see the before and after of each substitution, which can help identitify things like this.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions