How do I use variables in Oracle SQL Developer?
There are two types of variable in SQL-plus: substitution and bind.
This is substitution (substitution variables can replace SQL*Plus command options or other hard-coded text):
define a = 1;select &a from dual;undefine a;
This is bind (bind variables store data values for SQL and PL/SQL statements executed in the RDBMS; they can hold single values or complete result sets):
var x number;exec :x := 10;select :x from dual;exec select count(*) into :x from dual;exec print x;
SQL Developer supports substitution variables, but when you execute a query with bind :var
syntax you are prompted for the binding (in a dialog box).
Reference:
- http://www.oracle.com/technetwork/testcontent/sub-var-087723.html SQL*Plus Substitution Variables, Christopher Jones, 2004
UPDATE substitution variables are a bit tricky to use, look:
define phone = '+38097666666';select &phone from dual; -- plus is stripped as it is a numberselect '&phone' from dual; -- plus is preserved as it is a string
In SQL*Plus, you can do something very similar
SQL> variable v_emp_id number;SQL> select 1234 into :v_emp_id from dual; 1234---------- 1234SQL> select * 2 from emp 3 where empno = :v_emp_id;no rows selected
In SQL Developer, if you run a statement that has any number of bind variables (prefixed with a colon), you'll be prompted to enter values. As Alex points out, you can also do something similar using the "Run Script" function (F5) with the alternate EXEC syntax Alex suggests does.
variable v_count number;variable v_emp_id number;exec :v_emp_id := 1234;exec select count(1) into :v_count from emp;select * from emp where empno = :v_emp_idexec print :v_count;