Re: dynamic join view

"Jetlag" <jetlag11235_at_yahoo.com> wrote in message news:2eeed00e.0310230546.43067e7a_at_posting.google.com...
> Here is an example of what I would like to do.
>
> create or replace view V as
> select A.id, B.bb, C.cc
> from A, B, C
> where B.id (+) = A.id
> and C.id (+) = A.id;
>
> select id, bb
> from v;
>
> I would like the execution plan for the select statement to _not_
> involve table C because it is not needed in the results. However, now
> that I look at this, I realize that the database doesn't even know
> that this is the case ... unless there is at most ONE C.id for each
> A.id. As it turns out, this is the case in the query I am writing, so
> my question still stands ... I am just more doubtful now that it is
> possible.
>
> I understand dynamic SQL, and *think* I understand variable cursors.
> But as far as I know, a variable cursor is accessed only through
> PL/SQL ... using FETCH INTO. Is there a way to incorporate a variable
> cursor directly into an SQL statement, treating it like an SQL view?
>
> Thanks.
>
> -- jetlag --

IMHO .. no. What you are asking for is not possible. There can be cases when a table is joined to another table and the resultset from only one table is required.

You have you use ref cursors (open c1 for 'dynamically built sql string') to be able to do the thing you talk about.

Hop onto asktom.oracle.com and search for ref cursors ... you might find good examples.

Anurag Received on Thu Oct 23 2003 - 19:54:16 CDT