Re: how to use an object in a select statement?

Try this :
select r.ID from table(cast(func() as TY_MyType)) r;

A couple of points :
1. A table alias (in my case "r") is required!

2. Your type declaration seems invalid, you haven't given a name to the type

create or replace type TY_MyType as Object (   id number,
  test number,
  adate date
);

3. You are using 8i, I think you don't need to cast the function result to the type under 9i.

Paul Scott

On Fri, 25 Jul 2003 12:10:06 +0200, André Rothe <andre.rothe_at_imise.uni-leipzig.de> wrote:

>Hi,
>
>I wrote a function in package which returns an object like
>the following:
>
>create or replace type as object (
> id number,
> test number,
> adate date
>);
>
>The function works very well, but I cannot use the
>return value in a select statement.
>
>I tried:
>
>select pa.func().id from dual;
>
>But it doesn't work. How can I write a select statement,
>which use the attributes of the object?
>Can I use the return values without a temporary table
>in which I have to copy the values?
>
>Thank you
>Andre
Received on Fri Jul 25 2003 - 08:27:38 CDT