create package pa as
   type ra1 is record  (
         one integer,
         two integer
   );

   type ra2 is record  (
         r1 ra1,
         three integer,
         fore integer
   );
   type ta1 is table of ra1;
   type ta2 is table of ra2;

   function pa1 return ta1 pipelined;
   function pa2 return ta2 pipelined;  --pipelined functions must have a supported collection return type
end;

It doesn't seem possible too create a table of a record.

I explain why I want to do that. Perhaps you a another solution.

I have a big query with a "with statement" with n part.

Some parts are reused in others queries. I could rewrite these parts as views. But views doesn't accept parameters and the what is in the where block is long.

I want to define each step like that:

   function f_stepn(arg integer) return t_stepn
   is
   for c in (
          select r_stepnMinus1(stepnMinus1.* ) , o.f1,o.f2
          from
                f_stepnMinus1(arg) stepnMinus1 join othertable o on .....

   ) loop
      pipe row(c)
   end loop

In the end I do this this select :

   select skip(t.r_stepMinus1.r_stepMinus1.*), skip(t.r_stepMinus1.*), skip(t.*))

where skip in a polymophich function that delete the fields that are records.

P.S. the function in example in simplified. I can't do that : select r_stepnMinus1(stepnMinus1.* )

I know that I could rewrite the whole definition of r1 inside r2 but I don't want to write 2 times the same thing

CodePudding user response：

A Record is a PL/SQL-only data type and CANNOT be used in SQL statements.

A pipelined function is designed to be used in SQL statements and MUST return a collection that can be used in SQL.

When you return a collection of records, Oracle will implicitly create an OBJECT data-type that reflects the attributes of the record and will return a collection of this object rather than the record (and a collection of object can be used in SQL statements).

However, an OBJECT cannot contain an attribute with a RECORD data type. So when you try to create pipelined function returning a collection of records with a nested record attribute then Oracle cannot implicitly create an Object that reflects the record as the nested record is incompatible and so creating a PIPELINED function will fail.

Either:

• Use OBJECT data types created in the SQL scope (instead of records); or
• Do not nest records inside records.