1

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?

user6888062
  • 353
  • 1
  • 3
  • 16
  • You can define it as `SQL>define v1=2` and later can use as `SQL>select * from emp where Id=&v1`. – atokpas Oct 12 '16 at 08:13
  • 3
    [The documentation](https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_five.htm#i1211850) can help – Aleksej Oct 12 '16 at 08:15

3 Answers3

5

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
2

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
Michael Piankov
  • 1,989
  • 1
  • 8
  • 19
  • The OP asked specifically about bind variables - which are much better than substitution variables. And he/she asked about using them in a straight SQL query; what's with `refcursor` and such? –  Oct 12 '16 at 12:49
  • 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? – Michael Piankov Oct 12 '16 at 12:55
  • In the code and in the question - notice the colon before the names of the variables? `:v1` is a bind variable, there can be no confusion about that. –  Oct 12 '16 at 13:01
  • Looking at the question again, it could be made clearer by adding the word "bind" in a few places. I'll edit it in a minute to do just that. –  Oct 12 '16 at 13:14
0

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

anilech
  • 1,089
  • 8
  • 9