Hi Guru's,
Very Good Afternoon!!
I have schema X and its has three table A,B,C
WW role has been created to DELETE,INSERT and SELECT .
WW role has been assigned to Schema Y.
I have created synonym in Y schema for tables A,B,C as S1,S2,S3 repectively.
I am able to fire SELECT statement on (schema Y)sql window successfully like...
select * from y.s1;
select * from y.s2;
select * from y.s3;
When I am using these synonyms in package ,During COMPILATION package is getting "INVALID" state with error "TABLE or VIEW does not exist"
Example of PKG:
create or replace package body p1 as
------
-----
cursor c1 as
select * from
s1 inner join s2
on s1.r1=s2.r1
left outer join s3 /*(error for this line)*/
on s2.r1=s3.r1
-----
end;
I did some analysis and found on google that
Note:
To create without errors (to compile the procedure or package successfully) requires the following additional privileges:
- The owner of the procedure or package must be explicitly granted the necessary object privileges for all objects referenced within the body of the code.
- The owner cannot obtain required privileges through roles.
Questions:
1.How can i resolve this issue?
2. If i access with hardcoded schema name in package (Example: Y.s3) ,will this work?
3.Can someone provide more details on,what i found on google with example.
4.Why it failed for S3(Highlited in pkg example) not for other synonym(s1,s2) ?
Thanks very much