Re: no rows returned on execution

From: N Prabhakar <prabhs_at_po.pacific.net.sg>
Date: 1996/11/08
Message-ID: <560gk7$rie@newton.pacific.net.sg>#1/1

dmarsha3_at_csc.com (dmarsha3) wrote:
>group :
>
>I created a view as owner x which uses user_tab_columns as one of its
>tables. The works fine. But when I grant select on this view to user y
>it returns NO ROWS SELECTED. Looks like when the user y executes the
>oracle is using user_tab_columns of y instead of x. Is there any way
>to get around apart from using dba_tab_columns.
>
>Any help is appreciated.
>
>
>Thanks in advance.
>
>rama
>kramakri_at_dayton.csc.com

As you rightly pointed out, the view uses USER_TAB_COLUMNS of user y. Try using ALL_TAB_COLUMNS where owner = 'X' in the view. This will force the view to retrieve data from SCHEMA 'x'.

I believe you should have access to ALL_TAB_COLUMNS. It is same as in structure as USER_TAB_COLUMNS, you have a additional column OWNER. The OWNER is the user who owns that object.

Hope it helps.

Regards

N.Prabhakar
Socgen Crosby Securities
Singapore Received on Fri Nov 08 1996 - 00:00:00 CST