Re: Join on ref cursor?

"Keith Farmer" <kfarmer_at_thuban.org> wrote in message news:6d668d6.0208121242.3c570181_at_posting.google.com...
> 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.

They can't
At this point it is also unclear why you need to resort to a function and can't use ordinary sql.
I would recommend avoid resorting to 3GL techniques as much as possible and try to use SQL first.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address