错误的提示导致简单问题复杂化

测试用表：
SQL> create table t(x date);

Table created.

SQL> insert into t values(sysdate);

1 row created.

SQL> commit;

Commit complete.

 

1,ORA-01451: column to be modified to NULL cannot be modified to NULL
SQL> alter table t modify x null;
alter table t modify x null
                     *
ERROR at line 1:
ORA-01451: column to be modified to NULL cannot be modified to NULL
==>此提示让人很受伤。为什么不能更新为NULL？*)*)()_*)()

SQL> alter table t modify x not null;

Table altered.

SQL> alter table t modify x null;

Table altered.

2,ORA-01843: not a valid month
SQL> select to_date(x,'YYYYMMDD') from t;
select to_date(x,'YYYYMMDD') from t
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
==>此提示很明确。

SQL> alter session set nls_language='SIMPLIFIED CHINESE';

Session altered.

SQL> select to_date(x,'YYYYMMDD'),count(*) from t group by to_date(x,'YYYYMMDD');  
select to_date(x,'YYYYMMDD'),count(*) from t group by to_date(x,'YYYYMMDD')
                                                              *
ERROR at line 1:
ORA-01843: not a valid month
==>此提示让人很受伤。

SQL> delete from t;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select to_date(x,'YYYYMMDD'),count(*) from t group by to_date(x,'YYYYMMDD');

no rows selected
==>在没有数据时不报错----并不表示没有错误！！！
==>没数据时，to_date(null,'YYYYMMDD')并不报错；有数据时to_date(sysdate,'YYYYMMDD')就会报错。

SQL> insert into t values(sysdate);

1 row created.

SQL> commit;

Commit complete.

==>to_date改为to_char，更正错误后：
SQL> select to_char(x,'YYYYMMDD'),count(*) from t group by to_char(x,'YYYYMMDD');

TO_CHAR(   COUNT(*)
-------- ----------
20150629          1

 

总结：
1，错误提示会让人误入歧途，明确的错误提示非常重要；应用开发，日常沟通中，都存在类似情况。
2，同一个SQL，在没有数据的情况下成功运行，并不能说明没有错误；在特定异常情况下，还会发生错误。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-731673/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-731673/