引用系统保留字引起的PLS-00222-CSDN博客

本文通过示例展示了在Oracle数据库中，当表中存在名为`to_date`的列时，如何在PL/SQL中使用`to_date`函数而不会引发PLS-00222错误。解决方法是在调用函数时指定完整的对象引用，例如`standard.to_date`。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在表的定义中引用保留字引起的PLS-00222错误......

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;

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

转载于:http://blog.itpub.net/45259/viewspace-134937/