Home » SQL & PL/SQL » SQL & PL/SQL » View defination with schema name (Oracle 10.2.)
View defination with schema name [message #571636] Wed, 28 November 2012 07:34 Go to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
Hi,

I have a view in schema 'sales', let say the view name is view_test.

This view sales.view_test uses two base tables, one from schema 'products' and other from its own schema 'sales'.

So in sales schema the view defination is,

create or replace view view_test
as
select * from t_test, products.t_products
where t_test.id=t_products.id

The view got created, I have granted select permisson with grant option on tables t_test, products.t_products and view_test to another user 'master'.

But when i query the view from master schema I get an error saying that the view does not exists, upon analysis, I found one of the base table in the view defination has no schema name mentioned (select * from t_test, products.t_products --here only t_test mentioned rather sales.t_test), I feel because the table t_test belongs to the same schmea of the view, they did not mention the schema name.
Is there any solution to over come these kind of issues.
If not, does it mean that we need to always specify the schema names in the view defination for the base tables.

Regards,
Pointers
Re: View defination with schema name [message #571637 is a reply to message #571636] Wed, 28 November 2012 07:39 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I feel because the table t_test belongs to the same schmea of the view, they did not mention the schema name.


Correct.

Quote:
But when i query the view from master schema I get an error saying that the view does not exists


How did you query it?

With more than 260 posts you still don't how to post a question!

Regards
Michel

[Edit: typo]

[Updated on: Wed, 28 November 2012 08:03]

Report message to a moderator

Re: View defination with schema name [message #571639 is a reply to message #571636] Wed, 28 November 2012 07:45 Go to previous messageGo to next message
cookiemonster
Messages: 10909
Registered: September 2008
Location: Rainy Manchester
Senior Member
You could use alter session set current_schema to change the current schema to the owner of t_test.
However I'd go with putting the schema name in.
Re: View defination with schema name [message #571640 is a reply to message #571639] Wed, 28 November 2012 07:46 Go to previous messageGo to next message
cookiemonster
Messages: 10909
Registered: September 2008
Location: Rainy Manchester
Senior Member
Ignore my last post. It'll be complaining it can't find the view, not the underlying tables.
Re: View defination with schema name [message #571642 is a reply to message #571637] Wed, 28 November 2012 07:52 Go to previous messageGo to next message
pointers
Messages: 350
Registered: May 2008
Senior Member
@Michel:

I just logged into the schema master and queried like below

select * from sales.view_test 


Regards,
Pointers
Re: View defination with schema name [message #571644 is a reply to message #571642] Wed, 28 November 2012 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
With more than 260 posts you still don't how to post [a question]!

Regards
Michel
Re: View defination with schema name [message #571645 is a reply to message #571644] Wed, 28 November 2012 08:10 Go to previous message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's how you should post and demonstrate that you haven't done what you say you did:
SQL> show user
USER is "MICHEL"
SQL> create or replace view v 
  2  as
  3  select ename, dname 
  4  from emp e, scott.dept d
  5  where d.deptno = e.deptno
  6  /

View created.

SQL> drop user test cascade;

User dropped.

SQL> grant create session to test identified by test;

Grant succeeded.

SQL> grant select on v to test;

Grant succeeded.

SQL> connect test/test
Connected.
TEST> select * from michel.v;
ENAME      DNAME
---------- --------------
SMITH      RESEARCH
ALLEN      SALES
WARD       SALES
JONES      RESEARCH
MARTIN     SALES
BLAKE      SALES
CLARK      ACCOUNTING
SCOTT      RESEARCH
KING       ACCOUNTING
TURNER     SALES
ADAMS      RESEARCH
JAMES      SALES
FORD       RESEARCH
MILLER     ACCOUNTING

14 rows selected.

Regards
Michel
Previous Topic: how to gip zip using pl/sql
Next Topic: how can i return only one row of data on this query?
Goto Forum:
  


Current Time: Sat Aug 23 12:58:55 CDT 2014

Total time taken to generate the page: 0.14183 seconds