It is not anything to do with ANSI

It is a sqlplus'ism. SQLPlus is just a little command line tool that anyone could have written - it is just an OCI application.

and the implementors decided implementing a date type didn't make sense for them.

In my opinion - it would be confusing at best, what does a "date" look like. Please don't say "dd-mon-rr" - that isn't a date, that is a format. A date is a 7 byte internal format storing a century, year, month, day, hour, minute and second. You can only type in strings, strings need formats (a number is un-ambiguous, a date is not), in order to get a date therefore - you would need to use to_date() on a string - which is precisely what you are doing now already...

explain plan sees ALL BINDS as varchar2's. Regardless of what you used in your "variable" thing.

explain plan doesn't do binds like a normal select would - in explain plan the variables do not have to be defined, do not have to be set, are not peeked at.

consider:

ops$tkyte%ORA11GR2> set serveroutput off
ops$tkyte%ORA11GR2> create table t ( x varchar2(20) primary key, y varchar2(20) );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable x number
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where x = :x;

Execution Plan
----------------------------------------------------------
Plan hash value: 630007981

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    24 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |     1 |    24 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0013566 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

<b>the output of explain plan says "we shall index range scan".  But - how can that be - you are comparing a NUMBER to a STRING and when we do that - we convert the string into a number - it cannot really be using that index.

Explain plan sees the :x as a STRING - not as a number...  Read on and we can prove that</b>

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("X"=:X)

ops$tkyte%ORA11GR2> set autotrace off
ops$tkyte%ORA11GR2> select * from t where x = :x;

no rows selected

<b>Now I actually run the query - and using display cursor to pull up the plan of the last query executed in my session:</b>

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  bb7wdzt7s3c2q, child number 0
-------------------------------------
select * from t where x = :x

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    24 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

<b>I see NO INDEX was actually used and the reason:</b>

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("X")=:X)

<b>the implicit conversion that took place - we have an index on "X" but we are looking at to_number("X") - not "X" so the index cannot be used.. Explain plan didn't know that</b>


Note
-----
   - dynamic sampling used for this statement (level=2)


22 rows selected.

<b>In fact we can see that explain plan doesn't really care what you defined - OR DIDN'T define!</b>

ops$tkyte%ORA11GR2> explain plan for select * from t where x = :I_never_defined_this;

Explained.

<b>explain plan needs no bind variables - it is just looking at text in a query.
</b>

see
http://asktom.oracle.com/Misc/when-explanation-doesn-sound-quite.html

for more details.



You need to use to_date, to_number, to_timestamp, etc with explain plan

numbers are not ambiguous, dates are terribly so.

a) not sure what you are trying to get at here...

the presence or lackthereof of a TO_DATE function call could affect a query plan as an implicit conversion would have to take place otherwise and anytime you have implicit conversions taking place - you have something different happening at the lower level than it looks like at the higher level.

In general however, the string will be implicitly converted to a date using the current NLS_DATE_FORMAT in the session. So, even if you didn't have the to_date - we would be sticking one in there to execute the query.


b) yes, the to_date would affect the use of a stored outline - IF the stored outline query didn't have it but your test query did. The queries have to match.

explain plan limitation - it would happen with explain plan in sqldeveloper, in a java program you write, where ever.

You'll need to TO_DATE and TO_NUMBER everything that is a date or a number with explain plan - it sees everything as a string.

by default :b3+1 would be seen as string+number - which leads to to_number(string)+1 - which leads to a number output. You'd have to explicitly case the string to a date to get to_date(string)+1 which leads to date.

Yeah, but that's risky...

SQL> VAR DATE_VAL VARCHAR2(30)
SQL> EXEC :DATE_VAL := '31 MARCH 2018'

PL/SQL procedure successfully completed.

SQL> select to_Date(:date_val) from dual;

TO_DATE(:
---------
31-MAR-18

1 row selected.

SQL> EXEC :DATE_VAL := 'MARCH 31 2018'

PL/SQL procedure successfully completed.

SQL> select to_Date(:date_val) from dual;
select to_Date(:date_val) from dual
                *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected