Re: Select from a REF CURSOR

Hello Paul,

Here's an example:

create or replace type myType as object (   a int,
  b varchar2(10)
)
/

create or replace type myTable as table of myType; /

create or replace function f1 return myTable as

       l_data myTable := myTable();
begin
 for i in 1 .. 5 loop
   l_data.extend;
   l_data(i) := myType(i, 'Row #'||i );
end loop;
return l_data;
end;
/

select * from TABLE ( cast( f1() as myTable ) );

• ---------- 1 Row #1 2 Row #2 3 Row #3 4 Row #4 5 Row #5

Rgds.

"Paul Moore" <pf_moore_at_yahoo.co.uk> wrote in message news:u15gm0e0.fsf_at_yahoo.co.uk...
> This may be a dumb question, but I've never got my head round the
> strange TABLE(CAST(...)) syntax. I have a function which returns a REF
> CURSOR. What I'd like to do is to use this function in a SELECT
> statement. Something like:
>
> CREATE FUNCTION f(args) RETURNS SYS_REFCURSOR ...
>
> SELECT * FROM f(1,2,3);
>
> Is this possible, and if so what syntax should I be using?
>
> Thanks,
> Paul.
> --
> This signature intentionally left blank
Received on Fri Nov 07 2003 - 16:58:54 CST