Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Create view multiple schemes

Re: Create view multiple schemes

From: Karsten Farrell <kfarrell_at_belgariad.com>
Date: Fri, 28 Feb 2003 16:46:04 GMT
Message-ID: <MPG.18c9322beaac902c9896d7@news.la.sbcglobal.net>


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




ADDRESS


some emp name
some address

1 row selected.

SQL>

-- 
/Karsten
DBA > retired > DBA
Received on Fri Feb 28 2003 - 10:46:04 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US