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.
© 2021 Quest Software Inc. ALL RIGHTS RESERVED. Feedback Terms of Use Privacy