How to set Oracle bind variables when using SQLPlus?

Question

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?

Solution

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