Running a query with a bind-variable (e.g. :x) using F5 does not prompt for variable input. (52695)

I have table EMPLOYEES that has 22 rows of data. If I run in Toad using F5

SELECT * FROM Employees WHERE Last_Name = Grant;

I get result

EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-99 SA_REP 7000 .15 149 20

1 row selected.

... but

If I run SELECT * FROM Employees WHERE Last_Name = :x; in Toad using F5, Im not prompted for a variable and I get 0 row selected.

The issue has to do with the difference between bind-variables (:x) vs. substitution-variables (&x). You cannot run a query with a bind-variable and not declare it first.

For example, running SELECT * FROM Employees WHERE Last_Name = :x in SQL*PLUS (Oracles native tool) result in the following error.

SP2-0552: Bind variable X not declared.

If you set a variable as using semi-colon (e.g. :X), it means you are declaring a bind-variable. Using a bind-variable you must declare it.

For example:

Variable x varchar2(30);
EXEC :x := Grant;
select * from employees where last_name = :x;

Using F5 (execute as script) for the above will work and SQL*PLUS will also works.

If you set a variable using ampersand (e.g. &X), it means you are declaring a substitution-variable. Now, you DO NOT have to declare it and running SELECT * FROM Employees WHERE Last_Name = &x using F5 or in SQL*PLUS will result in you being prompted for input.

If using bind-variable, please run it using F9 in the Toad editor.