Below is an example of using variables in SQL Server 2000.
DECLARE @EmpIDVar INT
SET @EmpIDVar = 1234
SELECT *
FROM Employees
WHERE EmployeeID = @EmpIDVar
I want to do the exact same thing in Oracle using SQL Developer without additional complexity. It seems like a very simple thing to do, but I can't find a simple solution. How can I do it?
I am using the SQL-Developer in Version 3.2. The other stuff didn't work for me, but this did:
define value1 = 'sysdate'
SELECT &&value1 from dual;
Also it's the slickest way presented here, yet.
(If you omit the "define"-part you'll be prompted for that value)
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:
UPDATE substitution variables are a bit tricky to use, look:
define phone = '+38097666666';
select &phone from dual; -- plus is stripped as it is a number
select '&phone' from dual; -- plus is preserved as it is a string
var x and exec :x, no prompt.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
----------
1234
SQL> 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_id
print :v_count;
select...into (ORA-01006), so you'd need to do exec :v_emp_id := 1234; instead.
Ok I know this a bit of a hack but this is a way to use a variable in a simple query, not a script:
WITH
emplVar AS
(SELECT 1234 AS id FROM dual)
SELECT
*
FROM
employees,
emplVar
WHERE
EmployId=emplVar.id;
You get to run it everywhere.
Simple answer NO.
However you can achieve something similar by running the following version using bind variables:
SELECT * FROM Employees WHERE EmployeeID = :EmpIDVar
Once you run the query above in SQL Developer you will be prompted to enter value for the bind variable EmployeeID.
I think that the Easiest way in your case is :
DEFINE EmpIDVar = 1234;
SELECT *
FROM Employees
WHERE EmployeeID = &EmpIDVar
For the string values it will be like :
DEFINE EmpIDVar = '1234';
SELECT *
FROM Employees
WHERE EmployeeID = '&EmpIDVar'
You can read up elsewhere on substitution variables; they're quite handy in SQL Developer. But I have fits trying to use bind variables in SQL Developer. This is what I do:
SET SERVEROUTPUT ON
declare
v_testnum number;
v_teststring varchar2(1000);
begin
v_testnum := 2;
DBMS_OUTPUT.put_line('v_testnum is now ' || v_testnum);
SELECT 36,'hello world'
INTO v_testnum, v_teststring
from dual;
DBMS_OUTPUT.put_line('v_testnum is now ' || v_testnum);
DBMS_OUTPUT.put_line('v_teststring is ' || v_teststring);
end;
SET SERVEROUTPUT ON makes it so text can be printed to the script output console.
I believe what we're doing here is officially called PL/SQL. We have left the pure SQL land and are using a different engine in Oracle. You see the SELECT above? In PL/SQL you always have to SELECT ... INTO either variable or a refcursor. You can't just SELECT and return a result set in PL/SQL.
In sql developer define properties by default "ON". If it is "OFF" any case, use below steps.
set define on;
define batchNo='123';
update TABLE_NAME SET IND1 = 'Y', IND2 = 'Y' WHERE BATCH_NO = '&batchNo';
Use the next query:
DECLARE
EmpIDVar INT;
BEGIN
EmpIDVar := 1234;
SELECT *
FROM Employees
WHERE EmployeeID = EmpIDVar;
END;
Try this it will work, it's better create a procedure, if procedure is not possible you can use this script.
with param AS(
SELECT 1234 empid
FROM dual)
SELECT *
FROM Employees, param
WHERE EmployeeID = param.empid;
END;
