Dropping, recreating or compiling a package is failing with:

ORA-600 [PSDTYFND_WITH_SUFFIX]

The package includes pipelined functions.
(See Note.136909.1 PL/SQL Pipelined and Parallel Table Functions in Oracle9i)
Cause
The cause of this issue is not known. It is believed that importing into a database where these pipelined functions already exist may be the cause, but testing has failed to rep Solution
When a pipelined function is created a number of system generated objects are also created. These objects have the object_id of the package (not the package body) appended to the object name.

Example:

A package called PKG_RESULTSET:

SQL> select object_name, object_type, object_id
from dba_objects
where object_name = 'PKG_RESULTSET';

OBJECT_NAME OBJECT_TY
Example:

A package called PKG_RESULTSET:

SQL> select object_name, object_type, object_id
from dba_objects
where object_name = 'PKG_RESULTSET';

OBJECT_NAME OBJECT_TYP OBJECT_ID
----------------------- -------------------------- ------------------
PKG_RESULTSET PACKAGE 6601
PKG_RESULTSET PACKAGE BODY 6602

SQL
SQL> select object_name from dba_objects
where object_name like '%6601%';

OBJECT_NAME
--------------------------------------
SYS_PLSQL_6601_27_1
SYS_PLSQL_6601_46_2
SYS_PLSQL_6601_46_1
SYS_PLSQL_6601_54_1
SYS_PLSQL_6601_54_2
SYS_PLSQL_6601_62_1
SYS_PLSQL_6601_62_2
SYS_PLSQL_6601_9_1
SYS_PLSQL_6601_9_2
SYS_PLSQL_6601_DUMMY_1
SYS_PLSQL_6601_DUMMY_2

Note that the objects in this scenario that end with "_2" are all duplicates of the original set.

The solution is to manually drop each object belonging to the second set, i.e...

SQL> Drop type SYS_PLSQL_6601_46_2;
Type dropped.

SQL>Drop type SYS_PLSQL_6601_DUMMY_2;
Type dropped

SQL> drop package pkg_resultset;
Package dropped.

杰 说:
pipelined functions 啊

徐剑 说:
Comments:
--------------------
Tests have shown that the package could also be recompiled and recreated, but it would be more
prudent to drop the package and recreate it completely.
How to troubleshoot:

This function is usually called during recompilation, import or dropping a package involving PL/SQL type which Oracle needs to look up the object type in data dictionary. The error is commonly signaled especially during recompiled package with TABLE /Pipelined function for version below 11g.

User may verify if there were any duplicated pl/sql object t

1) Verify object_id of problem pl/sql object.
select object_name, object_type, object_id
from dba_objects
where object_name = ''

2) Verify if there were any object_name with the problem PL/SQL object id ends with '_2' suffix
select object_name from dba_objects
where object_name like '% 3) Drop type

4) Perform the original recompilation / imp or drop of the original problem pl/sql object.
- there is chances that
user may hit ORA-04043: object SYS_PLSQL__x_2 does not exist
i. Identify any synonyms with the same object_id
select owner, object_name, object_type
from dba_objects

ii. Drop all synonyms which are returned by this query, no matter which schema.

Then perform the original recompilation / imp or drop of the original problem pl/sql object again.