SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
C1 DATE

SQL> select * from t1;

C1
--------
20070428

SQL> update t1 set c1=to_date('20070501','yyyymmdd');

1 row updated.

SQL> commit;

Commit complete.

SQL> create or replace procedure p_test1
2 is
3 begin
4 update t1
5 set c1 = to_date('20070428', 'YYYYMMDD');
6 end p_test1;
7 /

Procedure created.

SQL> desc tmr
Name Null? Type
----------------------------------------- -------- ----------------------------
FROM_DATE NOT NULL VARCHAR2(8)
TO_DATE NOT NULL VARCHAR2(8)
OBU NOT NULL VARCHAR2(4)
PART_NO NOT NULL VARCHAR2(12)
LPK NOT NULL VARCHAR2(3)
REGISTER_DATE NOT NULL DATE
USER_ID NOT NULL VARCHAR2(10)
CREATE_DATE NOT NULL VARCHAR2(8)

SQL> update tmr set register_date=to_date('20070501','yyyymmdd');

0 rows updated.

SQL> create or replace procedure p_test2
2 is
3 begin
4 update tmr
5 set register_date=to_date('20070101','yyyymmdd');
6 end p_test2;
7 /

Warning: Procedure created with compilation errors.

SQL> show err
Errors for PROCEDURE P_TEST2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4 PL/SQL: SQL Statement ignored
5/22 PLS-00222: no function with name 'TO_DATE' exists in this scope
SQL>

检查后发现tmr表中存在to_date列名 ,这样导致oracle confused:

SQL> alter table t1 add (to_date date);

Table altered.

SQL> create or replace procedure p_test1
2 is
3 begin
4 update t1
5 set c1 = to_date('20070428', 'YYYYMMDD');
6 end p_test1;
7 /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE P_TEST1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4 PL/SQL: SQL Statement ignored
5/13 PLS-00222: no function with name 'TO_DATE' exists in this scope
SQL>

添加完整引用,指明具体对象:

create or replace procedure p_test2
is
begin
update tmr
set register_date=standard.to_date('20070101','yyyymmdd');
end p_test2;