PLS-00201: ID 'USER INPUT' must be declared
I get the error "PLS-00201: ID" CHICAGO "must be declared" when I try to enter user input in the prompt. "Please enter the region:" If I enter, for example, "CHICAGO" (no quote, of course) I am getting error PLS-00201. 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;
source to share
If you expect a string, you need to enclose the substitution variable in quotation marks when you use it:
V_CHILD REGIONS.CHILD_NAME%TYPE := '&p_1';
Be that as it may, it tries to interpret the replaced value as an identifier, that is, another variable or column name. So if you type CHICAGO
it will 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 displays the identifier. In fact, you could type 'CHICAGO'
, with quotes in your input, and it would work - but not ideal to remember.
If you're set verify on
into SQL * Plus or SQL Developer, you can see before and after each substitution, which can help identify such things.
source to share