Quick question: I imported a user from another DB and have got two package bodies, that are in invalid mode. I want to recompile them, but it throws an error that 'table or view doesn't exist'. I'm going through the body of that .. package body :-) and see that it tries to query a table, that belongs to another user.. I'm looking for this table in another schema and see that it exists. So I'm creating a synonym for that table, trying to recompile - the same story (table or view doesn't exist). I'm creating a public synonym - same here. I'm connecting as the user with the invalid packages and trying to do 'select * from that_table' and everything works.. But if i only want to recompile the package body - here comes 00942..
Please, advise what should I do to get this package body recompiled?
set role none
try to select from an affected table,
If you hit 942 again, you have your answer.
You have privilege through a role.
You either need direct privilege
set up the package using authid current_user
The disadvantage of the first solution is you end up with a myriad of grants.
This is why ideally only the table owner also owns the packages, and non-owners call the owner's packages.