使用to_date创建函数索引的时候经常会遇到ORA-01743错误

在使用to_date创建函数索引的时候经常会遇到ORA-01743错误。示例如下：

SQL> create table sunwg as select to_char(created,’yyyy’) yyyy,to_char(created,’mm’) mm from dba_objects;

表已创建。

SQL> create index ind_sunwg on sunwg(to_date(yyyy,’yyyy’));
create index ind_sunwg on sunwg(to_date(yyyy,’yyyy’))
                                        *
第 1 行出现错误:
ORA-01743: 仅能编制纯函数的索引
SQL> create index ind_sunwg on sunwg(to_date(mm,’mm’));
create index ind_sunwg on sunwg(to_date(mm,’mm’))
                                        *
第 1 行出现错误:
ORA-01743: 仅能编制纯函数的索引
SQL> create index ind_sunwg on sunwg(to_date(yyyy||mm,’yyyymm’));

索引已创建。

结果看起来有些奇怪吧。

ORA-01743: only pure functions can be indexed

Cause: The indexed function uses SYSDATE or the user environment.

Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL expressions must not use SYSDATE, USER, USERENV(), or anything else dependent on the session state. NLS-dependent functions are OK.

错误的提示看起来并不那么清晰。实际上这样的，to_date(yyyy,’yyyy’)和to_date(mm,’mm’)的结果和系统当前的sysdate有关系的。如果现在是5月份，那么to_date(yyyy,’yyyy’)的结果就是2008-05-01，如果现在是6月份，那么to_date(yyyy,’yyyy’)的结果就是2008-06-01。对于这样的有歧义结果的函数，函数索引是无法创建的。

转自：http://www.oratea.cn/2008/05/29/120.html

 

解决方法是自己实现一个to_date的外壳！！！！！！