今天早上突然收到同事一个询问,说遇到一个灵异事件,存储过程不能删除。我去查看一下情况:
SQL> drop procedure P_Information_Copy;
drop procedure P_Information_Copy
ORA-04043: object P_Information_Copy does not exist
在pl/sql中直接删除也是报P_Information_Copy不存在。然后我跑到dba_procedure里面去查看也没有这个存储过程
SQL> SELECT * FROM USER_PROCEDURES WHERE PROCEDURE_NAME='P_INFORMATION_COPY';
OBJECT_NAME PROCEDURE_NAME OBJECT_ID SUBPROGRAM_ID OVERLOAD OBJECT_TYPE AGGREGATE PIPELINED IMPLTYPEOWNER IMPLTYPENAME PARALLEL INTERFACE DETERMINISTIC AUTHID
-------------------------------------------------------------------------------- ------------------------------ ---------- ------------- ---------------------------------------- ------------------- --------- --------- ------------------------------ ------------------------------ -------- --------- ------------- ------------
他们猜测是跟大小写有关系。然后我进去查看存储过程:
CREATE OR REPLACE PROCEDURE "P_Information_Copy"
偶的神啊,原来存储过程里面加了两个双引号,马上加上双引号在删除。
SQL> drop procedure "P_Information_Copy";
Procedure dropped
OK啦,