How to access table in different schema in Store Procedure

On one Oracle database, there are several users or schemas. Each schema contains tables. These table belong to each user. It is possible for a user to access table in other schema. For instant, if there are three users in Oracle database, such as USER1, USER2, and USER3.

In event that the USER3 needs to access the table which belongs to the USER2. SQL select statement is written as

Select Col1, Col2, Col3 from USER2.TableName

This statement works fine when the SQL statement is run in Toad or SQL-Plus. However, if the SQL statement above is put into the store procedure, it will not work. The store procedure will not compiled correctly.

To correct this problem, the USER2 has to grant select on table privilege the USER3. Follow the step below:

1. Login to the Oracle database as USER2
2. Run this statement Grant Select on USER2.TableName to USER3;

Now the USER3 can put the select statement of granted table in the store procedure.