Forum Stats

  • 3,769,391 Users
  • 2,252,961 Discussions
  • 7,875,015 Comments

Discussions

View over multiple schemas

673711
673711 Member Posts: 1
edited Dec 3, 2008 9:54AM in SQL & PL/SQL
I have problem accessing a view which joins table over two schemas. I receive the following error:
"Error Code: 942, SQL State: 42000] ORA-00942: table or view does not exist"

I have two user schemas: ECS and ADSYS. I create tables, view and grants them according to following sample:

All commands was executed as sysdba with full acces to the database and all schemas (except where mentioned):
--CREATE TABLE AdSys.FirstTable ( FirstTableID VARCHAR2(10))
--CREATE TABLE Customer.CustomerFirstTable ( CustomerFirstTableID VARCHAR2(10))

--insert into adsys.firsttable values ('1')
--insert into customer.customerfirsttable values ('C')
--insert into customer.customerfirsttable values ('1')

--GRANT ALL on adsys.firsttable to ECS
--GRANT ALL on customer.customerfirsttable to ECS
--GRANT ALL on customer.customerfirsttable to ADSYS

--create view AdSys.FirstView as select firsttableid from adsys.firsttable
--create view AdSys.CustomerFirstView as select customerfirsttableid from customer.customerfirsttable

--GRANT ALL on adsys.firstview to ECS
--GRANT ALL on adsys.customerfirstview to ECS

--Changed user to ECS (which only has grant to tables in ECS and 2 two created views).
--select * from adsys.firstview
--select * from customer.customerfirstview

select on firstview works, but not on customerfirstview where I get the above error message. This is a sample which pinpoints the problem - the real case is a bit more complex where view is a join between 'firsttable' and 'customerfirstview', but I found out that a view across schemas was enough to recreate the problem.

The oracle version is 10g.

Any suggestions to why this doesn't work?

Thanx,
Micael Ericsson
Tagged:

Answers

  • 561825
    561825 Member Posts: 646
    --create view AdSys.FirstView as select firsttableid from adsys.firsttable
    --create view AdSys.CustomerFirstView as select customerfirsttableid from customer.customerfirsttable *******************************
    
    --GRANT ALL on adsys.firstview to ECS
    --GRANT ALL on adsys.customerfirstview to ECS
    
    --Changed user to ECS (which only has grant to tables in ECS and 2 two created views).
    --select * from adsys.firstview
    --select * from customer.customerfirstview *******************************************
    They are different.

    Regards

    Raj
This discussion has been closed.