How to define Oracle link variables when using SQLPlus?


How do I set Oracle bind variables when using SQLPlus?


SELECT orders.order_no FROM orders WHERE orders.order_date BETWEEN :v1 AND :v2

How do I set the dates of :v1 and :v2?

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;