ORA-00942 when compile procedure
532188Jun 9 2007 — edited Jun 9 2007Hi
may be that's is well known problem, but I can't find solution on forum quickly...
Details:
user X has objects: table, procedures and so on
user Y has no objects - to avoid creating huge synonyms to object in X I run "alter session set current_schema=X" on logon trigger for user Y. No problems with that - all working (objects can be accessed without using "X." prefix)
I created test procedure TEST_PROC in Y (copy of real proc in X) with
create or replace procedure Y.test_proc as
begin
...
-- select * from X.table1
...
end;
created succesfully
when I uncomment "select" statement in proc it's not compiled with ORA-00942: table or view does not exists.
BUT! grants ok:
grant select on X.table1 to Y;
"select * from table1" and "select * from X.table1" working well
why I'm geting ORA-00942 error in proc?