Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Join on ref cursor?

Join on ref cursor?

From: Keith Farmer <kfarmer_at_thuban.org>
Date: 12 Aug 2002 13:42:37 -0700
Message-ID: <6d668d6.0208121242.3c570181@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. Received on Mon Aug 12 2002 - 15:42:37 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US