Custom type spanning as many columns as it has fields in a DML statement

Say I have a custom datatype that has a number of members. For example:

create type myType as object(
  field1  number,
  field2  varchar2(50),
  field3  varchar2(25)
);

Now if I have a function that returns myType and put it in, say, a select statement, Oracle will return something like:

select myTypeFunction() from dual;

> myTypeFunction()
> -------------------------------------------------
> schemaowner.myType(1,'something','another thing')

Is there a generic way that I can span those three (in this example) fields into their own columns; so, in this case, I would like to have returned:

> field1  field2     field3
> --------------------------------
>      1  somethi开发者_如何学Pythonng  another thing

I know it's possible by calling the function multiple times. For example:

select myTypeFunction().field1,
       myTypeFunction().field2,
       myTypeFunction().field3
from   dual;

Or, by calling the function once in a subquery (or, similarly, using a table type):

select x.y.field1, x.y.field2, x.y.field3
from   (select myTypeFunction() y from dual) x;

-- or, where myTypeFunction has been modified to return a table of myType:

select field1,field2,field3 from table(myTypeFunction());

However, all these ways seem messy and inefficient -- with the last option perhaps looking the most attractive, barring alternatives -- so I would prefer to avoid doing them, if possible. Moreover, I believe that none of these ways would work if the function had parameters that were fed values from elsewhere; for example:

select x.field1, x.field2, x.field3
from   lookupTable alpha,
       table(anotherTypeFunction(alpha.id)) x;

or a more complicated join, where anotherTypeFunction obviously returns different values based upon its parameter(s). (Although I'm not very sure about this!)

Thanks :)

---

I'm not sure what you are looking for as you very much list the alternatives and then refuse to use any of them without solid argumentation. I don't think any of them is messy (a matter of personal opinion) or inefficient (I don't have any profiling to prove that either).

There should be no problems to pass parameters to myTypeFunction and anotherTypeFunction in your examples.

This example will give you the output you were asking. myTypeToTable is independent of members of myType.

create or replace type myType as object(
  field1 number,
  field2 varchar2(20),
  field3 varchar2(20)
);
/

create or replace type myTypeList as table of myType;
/

create or replace function myTypeToTable(obj in myType)
return myTypeList pipelined as
begin
  pipe row(obj);
  return;
end;
/

select * from table(myTypeToTable(myType(34, 'foo', 'and more foo')));

    FIELD1 FIELD2               FIELD3
---------- -------------------- --------------------
    34     foo                  and more foo

Hope this helps !