Re: Create view multiple schemes

Karsten Farrell <kfarrell_at_belgariad.com> wrote in message news:<MPG.18c7e8c56146fa3d9896d0_at_news.la.sbcglobal.net>...
> 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).

Hi Karsten,

Thx for your reply. But, this doesn't solve the problem; select is granted to the several schemes; When I run the Query (without Create View; just the select query) i get all results. I guess this means I do have the correct grants, don't I ? It goes wrong when adding the 'Create Or Replace View'

Any other suggestions ?

Thx,

Gerard

P.S. I guess the syntax is ok ? Received on Fri Feb 28 2003 - 04:56:25 CST