Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Join on ref cursor?
Since I couldn't locate any information, here's the problem I'm
facing. If someone has a good suggestion, please cc me at
kfarmer_at_thuban.org.
I have a report table with a schema equivalent to:
report_id
row_id
column_id
value
.. where the set of column_ids (and this is important) may vary based on the report's type. That is, both number and identity of the columns is variable.
It's not difficult to create a query dynamically which will reconstruct the source table represented thus.
What I'd like, if possible, would be a mechanism to allow this reconstructed table to be referenced in a query. Something like:
select *
from
(select report(1) from dual) r1,
(select report(2) from dual) r2
where r1.foo = r2.foo
would be acceptable, where report(report_id) returns the reconstructed table.
I'm trying to avoid creating a view for each report type, while also trying to avoid creating a view with every column (including those not used in a given report type). I'd considered a plsql function returning a ref cursor, but I don't know (nor do any of my coworkers) whether or not such beasts can be joined. Received on Mon Aug 12 2002 - 15:42:37 CDT