Re: How to include table from other schema in view?

That did the trick. Thanks.

Christopher

Sybrand Bakker <postmaster_at_sybrandb.demon.nl> wrote in message news:932593000.14401.0.pluto.d4ee154e_at_news.demon.nl...
> Hi Christopher,
> You need to connect as schema2 and grant select on schema2_table to
schema1
> or to public.
>
> Hth,
> Sybrand Bakker, Oracle DBA
>
> Christopher Allen <progplusSPAMBEGONE_at_gte.net> wrote in message
> news:YHql3.687$HF.18072_at_dfiatx1-snr1.gtei.net...
> > Can anyone tell me how to make a view include a table stored in another
> > schema (within the same database)? Creating a synonym doesn't help, and
> the
> > dblink is designed to reach into a different database...not a different
> > schema. I'm sure it's something simple, but after poring over the
> > documentation I haven't been able to find it.
> >
> > Here is an example of what I'm trying to do. It shows two small sample
> > tables--one in each schema. Then, in the second schema, it shows a
CREATE
> > VIEW command which incorporates the table in the first schema. The
> command
> > fails. How do I make it work?
> >
> > Thanks.
> >
> > ___________________________________________
> > connect schema1/pw_at_db_net8
> > Connected.
> > SQL> create table schema1_table (
> > 2 schema1_col1 number,
> > 3 schema1_col2 varchar2(20)
> > 4 );
> >
> > Table created.
> >
> > SQL> create public synonym schema1_table for schema1.schema1_table;
> >
> > Synonym created.
> >
> > SQL> connect schema2/pw_at_db_net8
> > Connected.
> > SQL> create table schema2_table (
> > 2 schema2_col1 number,
> > 3 schema1_col1 number,
> > 4 schema2_col2 varchar2(20)
> > 5 );
> >
> > Table created.
> >
> > SQL> create view schema2_view as
> > 2 select schema2_table.schema2_col1,
> > 3 schema2_table.schema2_col2,
> > 4 schema1.schema1_table.schema1_col2
> > 5 from schema2_table,
> > 6 schema1.schema1_table
> > 7 where schema2_table.schema1_col1
> > 8 = schema1.schema1_table.schema1_col1
> > 9 ;
> > schema1.schema1_table
> > *
> > ERROR at line 6:
> > ORA-00942: table or view does not exist
> >
> > SQL>
> >
> >
>
>
Received on Wed Jul 21 1999 - 17:12:40 CDT