EXPERT RESPONSE
A procedure sets its dependancies at compile time, not run time, so once a procedure is compiled, that doesn't change.
There is also an order of dependancy checking. Oracle first looks in the current schema for an object, then any private synonyms and finally any public synonyms.
So, if you compile a procedure and you have a table of name 'x' in your schema, it doesn't matter if anyone else has a table of that name. You procedure will always use the same 'x' table.
If you compile a procedure and you do not have a table named 'x' in your schema, Oracle will look to see if you have any private synonyms named 'x' and finally any public synonyms named 'x.' If it finds one it will always use that schema's 'x' even if you create a table in your schema afterwards.
The safest ways to ensure which schema's table you are using is to either prepend the schema owner to the table name, i.e. s2.t1, or to use "alter session set current_schema = s2;"
To use a table in another schema, you would need at least select permission on that table. To create a procedure using a table in another schema, you would need select granted directly to your user name and not to a role.
|
