Contains drop operation error ORA-20008 when alter package in oracle

In general, if we perform the alter operation, the drop operation is not triggered. However, in some special cases, the alter package operation can see the drop operation in recursive SQL.

We have a trigger in this environment, once there is a drop operation, it will report an error ORA-20008, and be blocked.

We can see that we only alter package. However, when it comes to its associated objects, the drop action occurs:
SQL> alter package MYUSER1.MYP _ IIA_IS_PACKAGE compile body;
Alter package MYUSER1.MYP _ IIA_IS_PACKAGE compile body
ORA-04045: errors during recompilation/revalidation of MYUSER1.MYP _ ABCD_OUN_PACKAGE
ORA-20008: This object is dependent on another object and cannot be deleted. Check dba_dependencies!
ORA-06512: at line 31

In trace 10046:

Parsing in cursor #10 len = 49 dep = 2 uid = 353 oct = 78 lid = 353 tim = 24012153459082 hv = 0 ad = '7d9f7d28'
Drop type "MYUSER1". Sys_plsql_315316_1679_1 force
END OF STMT

In the package, the pipelined pipe function automatically generates the type of SYS_PLSQL _ % s _ % c _ % v. % V indicates version.

During package re-compilation, these types need to be regenerate, the type of the old version will be deleted cyclically, and then create new version type. That is to say, during compilation, SYS_PLSQL _ % s _ % c_1 will be dropped first, and then create SYS_PLSQL _ % s _ % c_2; if it is compiled again, SYS_PLSQL _ % s _ % c_2 will be dropped, and then create SYS_PLSQL _ % s _ % c_1 ......

That is, pipelined is used in myuser1.myp _ abcd_oun_package, so the following type is generated:

Create or replace package body myuser1.myp _ abcd_oun_package is
......
Function splitstr (p_string in varchar2, p_delimiter in varchar2)
Return str_split
Pipelined
As
V_length number: = length (p_string );
V_start number: = 1;
V_index number;
Begin
While (v_start <= v_length)
Loop
V_index: = instr (p_string,

My package, myuser1.MYP _ IIA_IS_PACKAGE, will be referenced to myp_abcd_oun_package. Therefore, the type of MYP_IIA_IS_PACKAGE will be regenerate during compilation.

Solution:

First, the myp_abcd_oun_package is compiled successfully. When compiling other packages, the regenerate type is not required, and the trigger error is no longer reported.