PLS-00630 when trying to compile a pipelined function or the package that contains the pipelined function in 10g
Some days ago I came across the PLS-00630 when testing the migration of user schemas from a 9.2.0.8 database to a 10.2.0.4 database. The export and import activities went trough without a problem, but when I was trying to compile the invalid objects, the PLS-00630 was raised during the recompilation of a PL/SQL package body. The detailed explanation for this error is:
PLS-00630: pipelined functions must have a supported collection return type
Cause:
A pipelined function was specified with an unsupported return type. The following are not supported as return types of pipelined functions:- non-collections,
- PL/SQL tables,
- associative arrays,
- collections of PL/SQL types: rowid, mlslabel, long, long raw, boolean, binary_integer, pls_integer, string and urowidThe following restrictions apply:
- If the return type is a collection of records, then each of the attributes of the record must be a supported type.
- A collection of records must not contain a record type as one of its attributes.Action:
Specify a supported collection type as the pipelined function return type.
In my case, the package contained a pipelined function that did not followed all of the restrictions. The function worked fine in 9i, but the PL/SQL compiler in 10g got more restrictive and did not allow to compile. If you can not change the algorithm shortly, you can workaround this problem by setting the event 10946 during the compile in your session. So, for me the following commands solved the issue:
ALTER SESSION SET EVENTS = ’10946 trace name context level 4′;
ALTER PACKAGE mypackage COMPILE BODY;
This trick was tested with 10.2.0.4, but should also work with other patchset releases. BUT, to be on the safe side (because of future changes in the compiler), you should try to adopt the pipelined function according to the restrictions.
March 4, 2011 at 4:59 pm
Thanks, it helped me solve my problem too.