# Database Discussions > MDX and Analysis Services >  Oracle - Hung

## suma_killed

Hello ,

We have a problem running our clean up stored procedure along with our application. When this happens, both the application as well as the job that runs the stored procedure seems to hang. 

Here is the explanation of the problem:

The cleanup stored procedure deletes entries from a set of related tables as follows:

1.	DELETE FROM TABLE2  WHERE F1 IN ( SELECT F1 from TABLE1  tm WHERE F2 <  tm.currentdate AND tm.currentCount = 0); 
2.	DELETE FROM TABLE3 WHERE F1 IN (SELECT F1 from TABLE1  tm WHERE F2 <  tm.curentdate AND tm.currentCount = 0); 
3.	DELETE FROM TABLE4 WHERE (F1 IN (SELECT F1 from TABLE1  tm WHERE F2 <  tm.curentdate AND tm.currentCount = 0) AND CURRENTID IS NOT NULL); 
4.	DELETE FROM TABLE4 WHERE F1 IN (SELECT F1 from TABLE1  tm WHERE F2 <  tm.curentdate AND tm.currentCount = 0)
5.	UPDATE T_ TABLE1   SET CURRENTID =null WHERE (CURRENTID IN (SELECT  F1 from TABLE1   WHERE F2  < curentdate AND currentCount =0)); 
6.	DELETE FROM TABLE1  WHERE F2 < currentdate  AND currentCount = 0; 
7.	COMMIT;

The application inserts an entry into the 4 tables (TABLE1..TABLE4) in the following order:

1.	Invokes a stored proc to insert a row into table TABLE1
2.	Invokes a stored proc to insert a row into table TABLE3	
3.	Invokes a stored proc to insert a row into table TABLE4	
4.	Invokes a stored proc to insert a row into table  TABLE2	

The application I refer to is a Java application that uses CallableStatement to invoke the stored procedures and the java.sql.Connection has been configured with auto commit. Since, the application uses a connection pool, the stored procedures are executed in the context of different Connections. The problem happens only with Oracle and not with SQL server.

If you have come across this type of problem, please let me know how you have resolved this problem. Alternatively, if you identified the problem from the description, let me know your suggestions.

       Waiting for the precious reply.

Regards
SS

----------


## rmiao

Where did you run the sp? If run on Oracle, try post it in Oracle forum.

----------


## Island1

In any case, it doesn't belong here ...

Thanks.

Bill

----------

