| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Create view multiple schemes
gerard_at_kiwi-ict.nl said...
> 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 ?
>
I just did this little test (not the way I'd do it in real world) using Oracle8i...
SQL> connect system_at_dev2
Enter password: *******
Connected.
SQL> create user scheme1 identified by scheme1
2 default tablespace appdata
3 temporary tablespace temp;
User created.
SQL> grant connect, resource to scheme1;
Grant succeeded.
SQL> create user scheme2 identified by scheme2
2 default tablespace appdata
3 temporary tablespace temp;
User created.
SQL> grant connect, resource to scheme2;
Grant succeeded.
SQL> create user scheme3 identified by scheme3
2 default tablespace appdata
3 temporary tablespace temp;
User created.
SQL> grant connect, resource to scheme3;
Grant succeeded.
SQL> connect scheme1/scheme1_at_dev2
Connected.
SQL> create table employee (name varchar2(100));
Table created.
SQL> grant select on employee to scheme3;
Grant succeeded.
SQL> connect scheme2/scheme2_at_dev2
Connected.
SQL> create table address (address varchar2(100));
Table created.
SQL> grant select on address to scheme3;
Grant succeeded.
SQL> connect scheme3/scheme3_at_dev2
Connected.
SQL> create or replace view test as
2 select e.name, a.address
3 from scheme1.employee e, scheme2.address a;
View created.
SQL> desc test
Name Null? Type ----------------------------------------- -------- ---------------------------- NAME VARCHAR2(100) ADDRESS VARCHAR2(100)
SQL> select * from test;
no rows selected
SQL> connect scheme1/scheme1_at_dev2
Connected.
SQL> insert into employee values('some emp name');
1 row created.
SQL> commit;
Commit complete.
SQL> connect scheme2/scheme2_at_dev2
Connected.
SQL> insert into address values('some address');
1 row created.
SQL> commit;
Commit complete.
SQL> connect scheme3/scheme3_at_dev2
Connected.
SQL> select * from test;
NAME
1 row selected.
SQL>
-- /Karsten DBA > retired > DBAReceived on Fri Feb 28 2003 - 10:46:04 CST
 |
 |