How to use DATE type to bind variables in SQL*Plus

This article will explain in detail how to use DATE type binding variables in SQL*Plus, the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have some understanding of the relevant knowledge after reading this article.

SQL*Plus is an essential tool for DBA. According to the great god TK, "DBA that can't use SQL*Plus is not a qualified DBA." Of course, that's not what it said, but that's pretty much what it means. We can see the importance of SQL*Plus.

SQL*Plus is nice and powerful, but it also has some limitations, such as the DATE type cannot be used when binding variables are used in SQL*Plus.

Environmental preparation:

SQL > CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2 (30), MAKEDATE DATE)

Table created.

SQL > INSERT INTO T1 VALUES (1, 'Emma',TO_DATE (' 20180206)

1 row created.

SQL > INSERT INTO T1 VALUES (2, 'Kevin', TO_DATE (' 20170101)

1 row created.

SQL > COMMIT

Commit complete.

Now we need to use MAKEDATE as a condition to query:

SELECT * FROM T1 WHERE MAKEDATE: =: MD

We use VAR to declare variables:

SQL > VAR MD DATE

Usage: VAR [IABLE] [[NUMBER | CHAR | CHAR (n [CHAR | BYTE]) |

VARCHAR2 (n [CHAR | BYTE]) | NCHAR | NCHAR (n) |

NVARCHAR2 (n) | CLOB | NCLOB | BLOB | BFILE

REFCURSOR | BINARY_FLOAT | BINARY_DOUBLE]]

As you can see, variables that declare DATE types cannot be passed, and SQL*Plus gives feedback on how to use VAR. We can also see from the usage that there is really no DATE type. So how do you use a binding variable of type DATE? It can be done in a flexible way, as follows:

SQL > ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD'

Session altered.

SQL > VAR MD VARCHAR2 (8)

SQL > BEGIN: MD: = '20180206; END

2 /

PL/SQL procedure successfully completed.

SQL > SELECT * FROM T1 WHERE MAKEDATE =: MD

ID NAME MAKEDATE

1 Emma 20180206

Here, the VARCHAR2 type is used instead of the DATE type, and the implicit conversion of Oracle is used indirectly to realize the use of the DATE type (according to the implicit conversion rules, VARCHAR2 will be converted to the DATE type, for more information: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/Data-Type-Comparison-Rules.html#GUID-98BE3A78-6E33-4181-B5CB-D96FD9DC1694).

It should be noted here that the setting of NLS_DATE_FORMAT is incorrect, which will lead to the failure of implicit conversion.

On how to use DATE type binding variables in SQL*Plus to share here, I hope the above content can be of some help to you, you can learn more knowledge. If you think the article is good, you can share it for more people to see.