Oracle: why I can't select from VIEW using .?

Alin Source

There are some changes on the core product on which I'm working and some tables become now views and they are not working anymore because a view cannot be referenced with the schema name in front.

For example, the below will return an error: ORA-00942: table or view does not exist

select * from my_schema.my_view;

while a direct select from the view works fine

select * from my_view;

In case of a table, both scenarios above are working fine, is just the view that doesn't accept schema_name in front.

Why is that? Are there any decent workarounds?

EDIT: the selects are executed with my_schema user

oracleselectviewschema

Answers

answered 6 days ago Diego Souza #1

It's a grant issue.

grant all on my_view to my_schema

answered 6 days ago laf8 #2

Make sure your schema really is the owner by running:

Select * from all_objects where object_name = 'my_view';

I have a view stvytro with owner baninst1. There is a public synonym of the same name. The following both work:

select * from STVYTRO;
select * from baninst1.stvytro;

answered 6 days ago Alin #3

Thanks all for your help, especially @mathguy.

Basically the problem was that my_view was in fact a public synonym for my_view_r which was the actual view and being public, you cannot call it using the schema name in front like I was trying. eg:

select * from my_schema.my_view;

Maybe it will be helpful for others that are facing this issue in the future, the workaround would be to create a private synonym to the same view (my_view_r) using the schema name like below:

create synonym my_schema.my_view for my_view_r;

This is the only way to call a synonym using the schema name.

comments powered by Disqus