Problem Description
While compiling a package using "alter package ... compile" statement containing a pipelined function whose return type is based on a table belonging to a different schema may fail with:
ORA-00600: internal error code, arguments: [psdmsc.c: spawned type invalid]
When a similar package exists in a third schema. The error occurs when PL/SQL attempts to generate the global collection type for the function return type in the current schema.
The same error may also be seen in a database trace file, though not reported back to the client, when the package is first created.
With an example this issue is as follows:
SQL> grant create session, resource to u identified by u;
Grant succeeded.
SQL> grant create session, resource to u1 identified by u1;
Grant succeeded.
SQL> grant create session, resource to u2 identified by u2;
Grant succeeded.
SQL> connect u/u
Connected.
SQL> create table u_table ( id number , value varchar2(100));
Table created.
SQL> grant select on u_table to u1;
Grant succeeded.
SQL> grant select on u_table to u2;
Grant succeeded.
SQL>
SQL> connect u1/u1
Connected.
SQL> create or replace package u_pkg as
2 type u_tab is table of u.u_table%rowtype;
3 function f_u return u_tab pipelined;
4 end;
5 /
Package created.
SQL> alter package u_pkg compile;
Package altered.
SQL> connect u2/u2
Connected.
SQL> create or replace package u_pkg as
2 type u_tab is table of u.u_table%rowtype;
3 function f_u return u_tab pipelined;
4 end;
5
6 /
Package created.
SQL> alter package u_pkg compile;
alter package u_pkg compile
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [psdmsc.c: spawned type invalid], [], [], [], [], [], [], []
Cause of the Problem
This issue is due to Oracle bug. Oracle named this bug as 8425548. From the trace file we see following:
ksedst, dbkedDefDump, ksedmp, ksfdmp,
dbkeIncDump_kgsf, kgerinv_internal, kgerinv, kserin, psdtygen,
pcitygen, phpcmp, pcicmp0, kkxcmp0, rpiswu2, kkxcmp, kkpcrt, opiexe,
opiosq0, kpooprx, kpoal8, opiodr, ttcpip, opitsk, opiino, opiodr,
opidrv, sou2o, opimai_real
The reason it only fails when pipelined is because it has to create corresponding global types to match the plsql ones in order to allow it to be called from sql. As with the referenced bug something is going wrong with respect to these types.
Solution of the Problem
As the owner of the table on which the function's return type is based, manually GRANT EXECUTE ON..WITH GRANT OPTION to the system generated object type that matches the table%rowtype definition.
Run following query in order to find the extra object name created by owner u to which grant permission is needed.
SQL> set lines 140
SQL> col object_name for a40
SQL> select owner, object_name, object_type from dba_objects
2 where owner IN ('U','U1','U2') and
3 object_type = 'TYPE' and
4 object_name like 'SYS_PLSQL_%';
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ---------------------------------------- --------------------
U SYS_PLSQL_60110_18_1 TYPE
U SYS_PLSQL_60110_DUMMY_1 TYPE
U1 SYS_PLSQL_60111_9_2 TYPE
U1 SYS_PLSQL_60111_DUMMY_2 TYPE
U2 SYS_PLSQL_60120_9_1 TYPE
U2 SYS_PLSQL_60120_DUMMY_1 TYPE
Now connect as u user and execute grant permission.
SQL> conn u/u
Connected.
SQL> grant execute on SYS_PLSQL_60110_18_1 to U2 with grant option;
Grant succeeded.
SQL> conn u2/u2
Connected.
SQL> alter package u_pkg compile;
Package altered.
However this issue is fixed in patchset 11.2.0.3.
