How to include table from other schema in view?

From: Christopher Allen <progplusSPAMBEGONE_at_gte.net>
Date: Wed, 21 Jul 1999 21:11:20 GMT
Message-ID: <YHql3.687$HF.18072@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:11:20 CDT