Archive for category PL/SQL
Problem: You tried to execute an SQL statement that references a table or view that either does not exist, that you do not have access to, or that belongs to another schema and you didn’t reference the table by the schema name.
Lets suppose there are two schemas, one for all the tables and one for all the procedures and functions.
1. In order to make it clear, lets name them to be TABLE_SCHEMA and PROC_SCHEMA.
2. TABLE_SCHEMA has your business domain tables, say customer table, address table etc
3. PROC_SCHEMA has the procedures PROC_NAME that access these tables
If you try to connect to proc_schema to execute the procedures, you will get a message ORA-00942: table or view does not exist, even though the tables exist. In this case, proper grant permissions are not provided and synonyms are not created. Follow the below indicative steps to potentially resolve this issue
1. If you are using SQLDeveloper, connect to the proper schema to grant access as described below.
2. First connect to the table_schema where all the tables exists and run the below command
3. grant select,insert,update,delete, references on CUSTOMER to PROC_SCHEMA
4. Now DISCONNECT from TABLE_SCHEMA and connect to PROC_SCHEMA and run the below command to create synonym for the table in PROC_SCHEMA
5. create or replace SYNONYM PROC_SCHEMA.CUSTOMER FOR TABLE_SCHEMA.CUSTOMER
Lets extend the above concept and see what steps to take if we decide to execute the stored procedure PROC_NAME available in PROC_SCHEMA from another schema, say ANOTHER_SCHEMA
1. Connect to PROC_SCHEMA and run the below command
2. grant execute on PROC_SCHEMA.PROC_NAME to ANOTHER_SCHEMA
3. Disconnect from PROC_SCHEMA and connect to ANOTHER_SCHEMA and run the below command
4. create or replace SYNONYM ANOTHER_SCHEMA.PROC_NAME FOR PROC_SCHEMA.PROC_NAME
Also, take a look at the below tutorial, for further explanation