How do I set Oracle bind variables when using SQLPlus?
Example:
SELECT orders.order_no FROM orders WHERE orders.order_date BETWEEN :v1 AND :v2
How do I set the dates of :v1 and :v2?
3 Answers
Notice the following:
VARIABLE is a SQLPlus command. You don't end it with a semicolon (;).
In the VARIABLE command, you do not precede the variable name with colon (:).
Bind variable can't be of data type "date" - they are some sort of character value.
For that reason, IN YOUR CODE you must use
to_date()with the proper format model, or some other mechanism, to convert string to date. That is currently missing in your code. NEVER compare dates to strings!
Brief demo below.
SQL> variable v1 varchar2(20)
SQL> exec :v1 := '2015/12/22';
PL/SQL procedure successfully completed.
SQL> select 1 as result from dual where to_date(:v1, 'yyyy/mm/dd') < sysdate;
RESULT
----------
1
Comments
In common you may use define and use variable with &
define x = 12 ;
select &x from dual;
Or varable
variable x refcursor;
begin
open :x for select * from dual connect by level < 11;
end;
/
print x
4 Comments
Michael Piankov
The OP asked "How do I set Oracle variables when using SQLPlus?" Where you find word bind? And for his question I add two variants of solution. Am I wrong?
since 12.2 you don't need pl/sql to set the bind variable, set it direct in the VARIABLE command and save your shared pool:
SQL> variable s varchar2(10) = 'myString'
SQL> select :s from dual;
:S
--------------------------------
myString
SQL> variable s = 'anotherStr';
SQL> select :s from dual;
:S
--------------------------------
anotherStr
SQL>
BR
SQL>define v1=2and later can use asSQL>select * from emp where Id=&v1.