PLS-00630 unsupported collection return types / 10G upgrade
21 Dec
Posted by: gato in: Administration, DBA, Oracle
If you’ve just upgraded from 9i to 10g, and you are using unsupported collection return types in pipelined functions you are no longer able to compile the code due to a more restrictive 10g compiler. Your pipelined functions compiled just fine on 9i. You are unable to compile the functions with the following error message PLS-00630: pipelined functions must have a supported collection return type. If rewriting huge amounts of code is not an option for you, this event will allow you to compile the code:
ALTER session SET events= '10946 trace name context level 4';
One Response
karel Kolar
15|Mar|2010 1drop package ivan.x1;
drop type ivan.x11;
create or replace type ivan.x11 as object
(
aaa integer
,aaa2 varchar2(500)
,aaa3 date
,Constructor Function X11 Return Self As Result
)
;
/
CREATE OR REPLACE TYPE BODY IVAN.X11 AS
Constructor Function X11 Return Self As Result Is
Begin
Return;
End X11;
End;
/
create or replace package ivan.x1 as
type cca is record(
a integer);
type cc is table of ivan.x11;
type cci is table of integer;
type cc1 is record(
a integer
,b cc);
type a1 is table of cc1;
function aaa return a1
pipelined;
end;
create or replace package body ivan.x1 as
function aaa return a1
pipelined as
ret cc1;
ob ivan.x11 := ivan.x11();
ob_tab cc := cc();
begin
ob.aaa := 900;
ob_tab.extend;
ob_tab(ob_tab.LAST) := ob;
ob.aaa := 800;
ob_tab.extend;
ob_tab(ob_tab.LAST) := ob;
ret.a := 11;
ret.b := ob_tab;
pipe row(ret);
return;
end;
end;
select * from table (ivan.x1.aaa);
Leave a reply