Re: Create view multiple schemes

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 Received on Fri Feb 28 2003 - 07:47:42 CST