NEED FUNCTION to accept parameter, return "query-able" recordset

Yes, there is documentation on the web that talks about dynamic PL/SQL, ref cursors, etc. -- and indeed one or some combination of these might be what I need -- but I'm having trouble finding some concrete examples. I really don't want to do anything that awful complicated...

I would like a function (or something) that could accept one or two parameters and return a "temporary" recordset that I could in turn query (join to, outer join to, etc.). Pseudocode follows...

CREATE OR REPLACE make_temp_table(id INT) ... AS ...

    obj := select * from orders where customer_id = id;     return obj;
END; [Inside a package...]
SELECT *
FROM make_temp_table(12) x,
some_other_table y,
yet_another z
WHERE x.whatever = y.whatever
AND x.whatever_else = z.whatever_else(+)

Yes, I've desecrated the language here, but hopefully it's sufficient enough to explain my needs. Is it possible to do this or something close? What are my options? Where can I see some good samples or find some good instruction? We're using Oracle 8i.

Thanks, as always, in advance...

-jk Received on Tue Apr 30 2002 - 20:06:39 CDT