Posts Tagged Oracle

ORA-00942: table or view does not exist


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.

Solution:

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

http://www.adp-gmbh.ch/ora/err/ora_00942.html

,

Leave a comment

ORA-01436 CONNECT BY loop in user data


Problem:  When traversing a database table to find the hierachy of parent child (like manager, employee), this error happens

ORA-01436 CONNECT BY loop in user data

Solution:  If SQL Query looks like

select * from customer

start with manager_id = ‘334’

connect by prior emp_id = manager_id

above problem can happen, if the next record that is to be selected is a descendent of itself, which causes loop problem. By having another condition like below

 

select * from customer

start with manager_id = ‘334’

connect by prior emp_id = manager_id

AND relationship_cd = ‘IS MANAGER OF’

retrieves all the employees whose manager is either 334 and also those employees whose manager is children of manager 334

 

 

, ,

Leave a comment