ORA-00600: internal error code when compiling function returning Pipelined

Question

I have a package specification with code like below

CURSOR my_cur   IS      
select ...;   

TYPE mytype IS TABLE OF my_cur%ROWTYPE;   

FUNCTION myfun       
RETURN mytype  PIPELINED;

when I try to compile the package specificaiton, I get the following error:

ORA-00600: internal error code, arguments: [psdmsc.c: spawned type invalid], [0x1ED93D3C10], [3], [], [], [], [], [], [], [], [], []

The interesting part is that the body of the package containing this function compiles just fine.

I thought the resultset might cause problems but the query in my cursor returns at most 1 row

and without pipelined function the spec compiles without any errors? — hotfix, Jan 30 '20 at 16:29
yes. And if I put both the function spec and body into the package body all compiles well... — Coding Duchess, Jan 30 '20 at 16:58

score 1 · Answer 1 · answered Jan 30 '20 at 20:24

This issue might be met within these version range for DB.

The error might arise due to the compiling a pipelined function returning a PL/SQL table of my_cur%ROWTYPE when the following situations met :

the cursor[ or table] my_cur referenced in my_cur%ROWTYPE is owned by a different schema than the function
there is another function in another schema having the same name as the failing function
this other function refers to the same cursor[ or table] as the failing function,
this other function is either in the same schema as the cursor[ or table] or in a different one.

For a workaround, connect to sys [or system], and check out the objects with invalid status by

SQL> SELECT owner,object_name,status
  FROM dba_objects
 WHERE status = 'INVALID'
   AND object_type = 'TYPE'
   AND object_name LIKE 'SYS_PLSQL_%' 

OWNER OBJECT_NAME          STATUS
----- -------------------- --------
USER1 SYS_PLSQL_12345_2_1  INVALID

then compile the type returned

SQL> alter type USER1.SYS_PLSQL_12345_2_1 compile;
SQL> show error

might return PLS-00201 for another type such as

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/44 PLS-00201: identifier 'USER2.SYS_PLSQL_12346_7_1' must be declared

then grant the execute privilege to the owner of the invalid type

SQL> grant execute on USER2.SYS_PLSQL_12346_7_1 to USER1;

then recompile the invalid type

SQL> alter type USER1.SYS_PLSQL_12345_2_1 compile;
SQL> show error

and recheck out the objects with invalid status by the above query, if returns no row, then the concerned package can safely be compiled

SQL> alter package USER1.mypackage compile;

Ref. Doc ID 1185303.1 from Oracle Support

I do not have access to dba_objects. Also I did find that article you are mentioning and none of the cases listed fit here. — Coding Duchess, Jan 31 '20 at 12:53
@CodingDuchess The Document tells us that *After compiling the invalid type, the error PLS-00201 is raised. This is the issue described in Bug 8425548 ORA-00600 [PSDMSC.C SPAWNED TYPE INVALID] WHILE COMPILE PACKAGE* — Barbaros Özhan, Jan 31 '20 at 15:37

score 0 · Accepted Answer · answered Jan 31 '20 at 15:11

Found an issue - in my cursor query one of the aliases was a reserved keyword. Using escape characters did not prevent it from being seen as a reserved keyword. The interesting thing was that query worked fine on its own but prevented the function from compiling

ORA-00600: internal error code when compiling function returning Pipelined

2 Answers2