SP2-0552: Bind variable "..." not declared.

I was creating a SQL*Plus script for testing the results of a new report. The script prompted the me for some values and then ran some queries. I then was going to compare those query results with the results of the new report. Pretty basic. I have done this type of thing many, many times before.

The script issues the ACCEPT command to prompt and receive my test values. A SELECT statement runs to query the database. Here is a sample SQL*Plus script based on our friend the EMP table:

ACCEPT p_emp_no char PROMPT "Enter employee ID: "

select *
from emp
WHERE empno = &p_emp_no;

I would run the script in SQL*Plus (Oracle 9.2.0.4.0) and get:

SP2-0552: Bind variable "P_EMP_NO" not declared.

Ok, what the heck is going on? Well it ends up an hour or so earlier I had ran another SQL*Plus script which set SCAN off (SET SCAN OFF) because the prior script had an ampersand (&) in one of the remarks. Well with SCAN set OFF, SQL*Plus was somehow converting '= &p_emp_no" into "= :p_emp_no" and looking at p_emp_no as if it were an undefined bind variable. I discovered this when I finally exited SQL*Plus and reentered and reran the script and it worked. So I added SET SCAN ON to my script and it now works!!

Mike