刪除包時，報ORA-04043: object SYS_PLSQL×× dose not exists

環境
OS： SunOS HOSTNAME 5.10 Generic_127111-11 sun4u sparc SUNW,Sun-Fire-V890
DB：Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production

問題：一個包處於無效狀態，通常直接用命令編譯一下，就行了，但這個簡單的操作，卻碰到:
SQL> alter package SCHEMA.PACKAGENAME compile;
alter package SCHEMA.PACKAGENAME compile
*
ERROR at line 1:
ORA-04043: object SYS_PLSQL_6459_DUMMY_2 does not exist

這個錯誤，其實在9i裡這是比較常見的錯誤，在SP裡定義：TYPE和TABLE OF TYPE的資料物件時.Oracle就會自動的在系統內生成SYS_PLSQL_%物件,好象Pipeline也會產生.當找到不這些SYS_PLSQL物件時，這個SP就無法通過編譯了.

SQL> select 'drop type SCHEMA.'||object_name||' force;' from dba_objects where object_name like 'SYS_PLSQL_6459%';
drop type SCHEMA.SYS_PLSQL_6459_15_1 force;
drop type SCHEMA.SYS_PLSQL_6459_DUMMY_1 force;

這裡確實沒有錯誤提示裡所指的物件。

解決辦法,把這個SP對應的SYS_PLSQL物件全刪除：
SQL> drop type SCHEMA.SYS_PLSQL_6459_15_1 force;

Type dropped.

SQL> drop type SCHEMA.SYS_PLSQL_6459_DUMMY_1 force;

Type dropped.

SQL> alter package SCHEMA.PACKAGENAME compile;

Package altered.

確認一下，有沒有重新生成SYS_PLSQL:

SQL> select 'drop type SCHEMA.'||object_name||' force;' from dba_objects where object_name like 'SYS_PLSQL_6459%';
drop type SCHEMA.SYS_PLSQL_6459_DUMMY_2 force;
drop type SCHEMA.SYS_PLSQL_6459_15_2 force;
drop type SCHEMA.SYS_PLSQL_6459_DUMMY_2 force;

這就對應Package header裡定義了三個TYPE:
TYPE invcur_t IS REF CURSOR RETURN trinvoicemaster%ROWTYPE;
TYPE inv_lines IS TABLE OF trinvoicemaster%ROWTYPE;
FUNCTION split_invoice ( cur_invoicemast in invcur_t ) RETURN inv_lines pipelined;

要刪除這些SYS_PLSQL有時比較難的，記得有次用命令死活也刪除不了，最後藉助TOAD，在圖形裡把它刪除了。Package所引用的SYS_PLSQL物件怎麼會不見了，我記得這是一個BUG. 10G之後，就沒有碰到這個問題了。