Bind a variable of DATE type in SQL * Plus

Bind a variable of DATE type in SQL * Plus

SQL * Plus is an essential tool for DBAs. According to TK, "DBAs that do not use SQL * Plus are not qualified DBAs ." Of course, this is not the case, but it is similar to this. We can see the importance of SQL * Plus.

SQL * Plus is very good and powerful, but it also has some limitations. For example, when SQL * Plus is used to bind a variable, the DATE type cannot be used.
Environment preparation:
SQL> CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2 (30), MAKEDATE DATE );

Table created.

SQL> INSERT INTO T1 VALUES (1, 'emm', TO_DATE ('20140901', 'yyyymmdd '));

1 row created.

SQL> INSERT INTO T1 VALUES (2, 'kevin ', TO_DATE ('201312', 'yyyymmdd '));

1 row created.

SQL> COMMIT;

Commit complete.

Now we need to use MAKEDATE as the condition for query:
SELECT * FROM T1 where makedate: =: MD;

We use VAR to declare variables:
SQL> VAR MD DATE
Usage: VAR [IABLE] [<variable> [NUMBER | CHAR (n [CHAR | BYTE]) |
VARCHAR2 (n [CHAR | BYTE]) | NCHAR (n) |
NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE
REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE]
As you can see, variables declared for the DATE type cannot pass. SQL * Plus provides a feedback on VAR usage. From the usage method, we can also see that there is no DATE type. So how to use the DATE binding variable? You can use a work und as follows:
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyymmdd ';

Session altered.

SQL> VAR MD VARCHAR2 (8)
SQL> BEGIN: MD: = '20140901'; END;
2/

PL/SQL procedure successfully completed.

SQL> SELECT * FROM T1 WHERE MAKEDATE =: MD;

ID NAME MAKEDATE
------------------------------------------------
1 Emma 20180206

Here, VARCHAR2 is used to replace the DATE type, and Oracle implicit conversions indirectly implement the use of the DATE type. (According to implicit conversion rules, VARCHAR2 is converted to the DATE type. For details, refer: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Data-Type-Comparison-Rules.html#GUID-98BE3A78-6E33-4181-B5CB-D96FD9DC1694 ).

Note that the setting of NLS_DATE_FORMAT is incorrect, leading to implicit conversion failure.