SELECT 문으로 table 검색이 가능한데도, 프로시저나 함수 안에서는 사용이 불가능한 경우
- ROLE로써 해당 table접근 권한을 받은 경우는 프로시저나 함수에서 사용이 불가능하다. 이럴경우 table소유자가 직접 권한을 부여해주어야 한다.
참조There's a strong chance that the privileges to select from table1 have been granted to a role, and the role has been granted to you. Privileges granted to a role are not available to PL/SQL written by a user, even if the user has been granted the role.
You see this a lot for users that have been granted the dba role on objects owned by sys. A user with dba role will be able to, say, SELECT from V$SESSION, but will not be able to write a function that includes SELECT FROM VSESSION. The fix is to grant explicit permissions on the object in question to the user directly, for example, in the case above, the SYS user has to GRANT SELECT ON V_SESSION TO MyUser;
https://stackoverflow.com/questions/6669575/ora-00942-table-or-view-does-not-exist-works-when-a-separate-sql-but-does-not
