But why?
stephan, February 02, 2011 - 8:59 am UTC
Is there a reason for this? Some ANSI standard?
Obviously we can get around it by doing exactly what you stated, but this seems like missing functionality to me...
February 02, 2011 - 10:08 am UTC
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...
A reader, February 02, 2011 - 11:06 am UTC
If I use the variable to generate explain plan, will it have an impact on the plan?
For example my original query is:
select c1 from t1 where c1 = :x ;
c1 is type DATE. The query does not need a TO_DATE for variable x since I have already declared it as type DATE in the stored procedure and populated it with a date.
Now I will have to rewrite my query as:
var x VARCHAR2
exec :x := '2/1/2011';
select c1 from t1 where c1 = to_date(:x,'mm/dd/yyyy');
Any issues with doing this?
Thanks...
February 03, 2011 - 1:58 pm UTC
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
What's about BINARY_DOUBLE etc. then?
Sergei.Agalakov, February 02, 2011 - 12:26 pm UTC
The answer technically is correct, but the reasoning seems to be flawed to me. Other types also have some binary internal representations, and what we see on the screen isn't what is saved in database. Yes
1.7d
is shorter than
to_date('2010-02-02','YYYY-MM-DD')
but in the essence both convert strings to the internal Oracle data format.
My guess is that there wasn't any particular reason to exclude DATE and include NUMBER or BINARY_FLOAT. It is just some unimplemented feature, and after 20+ years there are slim chances it will be implemented.
February 02, 2011 - 1:28 pm UTC
numbers are not ambiguous, dates are terribly so.
Igor, February 02, 2011 - 12:53 pm UTC
Hi,
I also think that is underestimated importance of having it feature-wise up to date.
There is much more that can be said about it, but won't make it longer here...
Regards
A reader, February 02, 2011 - 2:17 pm UTC
Thanks Tom for an excellent explanation.
a) If I was trying to generate a 10046 trace instead of just explain plan, will the query plan be affected by to_date?
b) If I was trying to test whether a stored outline is used or not, will it be affected by the presence of to_date? My guess is that since I created the stored outline without to_date, in the presence of to_date, the stored outline will not be used.
Thanks...
February 03, 2011 - 2:01 pm UTC
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.
Date in sqlplus would be nice
Galen Boyer, February 02, 2011 - 3:15 pm UTC
What would it look like?
variable v_dt date
exec :v_dt := to_date('01/01/1992','MM/DD/YYYY');
select * from some_table where some_dt = :v_dt;
Then, you wouldn't be to_date'n everytime you use the variable. Would be nice.
But, to be honest, its not high on my sqlplus features I'd like to see. Having a parent/child process relationship just like shell would be fantastic. set define off, set define on, set define off, set define on ... If executing a script didn't affect the parent sqlplus session, man, oh man, that would be sweet. Maybe a "dot'n in" concept? Whohoo!!!
date in sqlplus
Sokrates, February 03, 2011 - 1:51 am UTC
I am missing that since ages ...
Galen, there are so many features sqlplus is missing.
It seems to me sometimes they don't know how to spell usability when it comes to sqlplus
A reader, February 03, 2011 - 9:54 am UTC
Tom,
You said that : explain plan sees ALL BINDS as varchar2's.
Unfortunately this has a side effect when using DATE variables:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> drop function my_func;
Function dropped.
SQL> CREATE OR REPLACE FUNCTION MY_FUNC (
2 P1 IN NUMBER,
3 P2 IN NUMBER,
4 P3 IN DATE)
5 RETURN VARCHAR2 IS
6
7 V_OUT VARCHAR2(1);
8
9 BEGIN
10
11 BEGIN
12 select '1' into v_out from dual;
13
14 EXCEPTION
15 WHEN NO_DATA_FOUND THEN
16 V_OUT := 'X';
17 END;
18
19 RETURN V_OUT;
20
21 END MY_FUNC;
22 /
Function created.
SQL> explain plan for select my_func(:b1,:b2,:b3) from dual;
Explained.
SQL> explain plan for select my_func(:b1,:b2,:b3+1) from dual;
explain plan for select my_func(:b1,:b2,:b3+1) from dual
*
ERROR at line 1:
ORA-06553: PLS-306: wrong number or types of arguments in call to 'MY_FUNC'
SQL>
Since explain plan treats all binds as VARCHAR2s, when I add 1 to :B3, Oracle throws an error. Since :B3 is actually declared as type DATE, when the code executes, Oracle just add 1 day to the date passed and works as expected. Is this a limitation of explain plan or SQLPlus?
Thanks...
February 03, 2011 - 3:34 pm UTC
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.
You don't always have to use TO_DATE
Oliver, June 06, 2018 - 12:33 pm UTC
If you use a long date format in your variable initialisation, you don't always need to use TO_DATE, as shown below:
SQL>VAR DATE_VAL VARCHAR(30);
SQL>EXEC :DATE_VAL := '31 MARCH 2018'
In some cases, you can use the date without needing to use TO_DATE:
SQL> CREATE TABLE TEMP_DATE (DATE1 DATE NULL);
SQL> INSERT INTO TEMP_DATE VALUES (:DATE_VAL);
SQL> COMMIT;
SQL> SELECT TO_CHAR(DATE1, 'DD/MM/YYYY') FROM TEMP_DATE;
TO_CHAR(DA
----------
31/03/2018
June 07, 2018 - 1:30 am UTC
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