Re: Create view multiple schemes

mvk_at_servocomp.ru (Michael Kuznetsov) wrote in message news:<1543a3e7.0302280547.7c1debdf_at_posting.google.com>...
> gerard_at_kiwi-ict.nl (Gerard Verbruggen) wrote in message news:<f59390e.0302280256.195b02b7_at_posting.google.com>...
> > 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 ?
>
> Hi Gerard,
>
> Karsten gave you correct solution with grant select. It must work. I
> have similar views in Oracle 8.1.7. They work perfectly.
>
> Check carefully your view. If select statement works view based on the
> select should also works.
>
> You can try to create synonyms in scheme Scheme3 for tables in schemas
> 1 and 2. It simplifies syntax of your view.
>
> Regards,
> Michael
> Brainbench MVP for Oracle Programming
> http://www.brainbench.com

Thx Micheal,

I will visit with this company again next tuesday. I will check everything again, and will post the results here. Do I understand you correctly when I say: If this works:
SELECT

         E.Name, 
         A.Address 
FROM 
         Scheme1.Employee E, 
         Scheme2.Address A 

This should work as well:

CREATE OR REPLACE VIEW TEST AS
SELECT

         E.Name, 
         A.Address 
FROM 
         Scheme1.Employee E, 
         Scheme2.Address A 

Assuming that in both cases I log in as the same user, in the same scheme etc.

Thx again, and I will check again.

Gerard Verbruggen Received on Fri Feb 28 2003 - 13:34:19 CST