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

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 - 16:36:58 CDT