Re: Create view multiple schemes

gerard_at_HateSpam.kiwi-ict.nl said...
> Hi All,
>
> A company I work for has several schemes in an Oracle 8i database. I'm
> trying to create a view over several schemes, but only get errors.
> Maybe one of you can help me.
>
> Asume:
> Scheme1 with table Employee
>
> Scheme2 with table Address
>
> I log into Scheme3, Where I want to create the view:
>
> SQL statement:
> CREATE OR REPLACE VIEW TEST (or should this be Scheme3.TEST ?) AS
> SELECT
> E.Name,
> A.Address
> FROM
> Scheme1.Employee E,
> Scheme2.Address A
> WHERE
> etc. etc.
>
> I get the error that Table Scheme2.Address doesn't exist. When I log
> into Scheme2, and try to create the view here, I get the error that
> Scheme1.Employee doesn't exist.
>
> I'm pretty familiar with SQL-Server, and I've done this several times
> without a problem. New to Oracle.... So, probably I'm forgetting
> something here ?
>
> Thx,
>
> Gerard Verbruggen
>
> P.S. I also tried to login as System (the topmost user) and get the
> same problems.
>
>

1. Connect to scheme1 and grant select on employee to scheme3 (or better yet, to a role, which you assign to scheme3).
2. Connect to scheme2 and grant select on address to scheme3 (or better yet, to a role, which you assign to scheme3). -- /Karsten DBA > retired > DBA