Re: ORA-00942 on view containing ANSI join
- From: Rumpi Gravenstein <rgravens@xxxxxxxxx>
- To: nkodner@xxxxxxxxx
- Date: Thu, 10 Dec 2009 07:53:42 -0500
I can't comment on whether this is a bug beyond saying no, no, no...
never mix ansi and traditional join syntaxes like this. Either go all
ANSI which requires the INNER JOIN or all traditional. In my
experience ANSI joined tables can not see tables mentioned in the
traditional format which explains your issue. In any case you are in
uncharted waters with mixed syntax joins.
If you were to do this with all ANSI it should look something like:
SELECT <col>...
FROM table a
INNER JOIN table b
ON ( a.col = b.col )
LEFT OUTER JOIN table c
ON ( c.col = b.col)
WHERE ...
I've got a presentation and paper that walks you through the
traditional vs. ansi join logic and discusses some of the issues.
Find that paper here http://tinyurl.com/yb5bq82
On Wed, Dec 9, 2009 at 5:25 PM, Neil Kodner <nkodner@xxxxxxxxx> wrote:
> Version 10.2.0.4
> User A owns a handful of tables, User B has select privileges on the tables
> through both PUBLIC and role SELRAWEREP. User B contains no synonyms to any
> of User As tables or views. It is confirmed that User B can query the
> necessary tables when qualifying with the table owner.
> User A creates a somewhat complex view (view 1) with a few nested
> subqueries, ANSI-style-outer-joined to the other tables in the view's FROM
> clause. User A then grants select on the view to both User B directly, and
> to SELRAWEREP. User B tries to select from the view and receives ORA-00942.
>
> I then create a new version of the view and fully-qualify each table name
> with the table owner(view 2). User B can query the view just fine.
> I remove the references to the table owner and then rework the ANSI
> Outer-join to a standard Oracle-style inner join.(view 3). User B can query
> that view just fine.
> I then create view4 which is a copy of view 3 but outerjoining the subquery,
> which matches the intent of the original view. User B is back to receiving
> ORA-00942 when trying to query it.
> Finally, I modify the original view and change the ANSI outer join into an
> ANSI equi-join, user B still gets ORA-00942.
>
> My views may be found at http://www.neilkodner.com/views.txt
> I can't find the documentation that explains what's going on here. I can't
> tell if this is an outer-join issue, an issue with combining ANSI-style
> joins with traditional Oracle joins, or something else. Does anyone have an
> idea, or can at least cite the documentation that explains all of this?
--
Rumpi Gravenstein
--
//www.freelists.org/webpage/oracle-l
Other related posts:
