Re: viiews across schemas?

A copy of this was sent to Douglas Nichols <dnichols_at_fhcrc.org> (if that email address didn't require changing) On Tue, 12 Oct 1999 17:28:39 +0000, you wrote:

>I have been trying to create a view from schemaX in schemaY but cannot
>do it.
>create view schemaY.viewX as select * from schemaX.tableX;
>
>ERROR at line 1:
>ORA-00942: table or view does not exist
>
>But I can create a table from schemaX in schemaY then create a view.
>create table schemaY.tableX as select * from schemaX.tableX;
>create view schemaY.viewX as select * from schemaY.tableX;
>
>Can I reference a table across a schema with a view?

The reason is because you cannot create a stored object that references objects you can see because of a role.

You must be able to "see" schemaX.tableX because some role you have allows you to. You cannot create a stored object (view, procedure, etc) that references this object.

In order to create the view, you must have been granted access to the object directly.

grant select on tableX to schemaY;

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Oct 12 1999 - 13:29:04 CDT