Thanks a lot it worked perfectly.

The problem is not so much the invalidation of the procedure in the shared pool, but the consequent discarding of the corresponding instantiation in your session's UGA. Recompiling the procedure does not change the fact that your existing instantiation of that procedure will have to be discarded when it is next referenced. However, that will only happen once. So all that you need to do is to write an exception handler for this error that retries the call. It will succeed on the second attempt.

I've got following problem with my stored procedure: Inside the procedure I have an EXECUTE IMMEDIATE which dynamically drops and later on creates an index on a table referenced by this procedure and few other procedures. The reason for doing this is to speed up a bulk update on the indexed column (time difference is 2.5 minutes versus 30 minutes, so it's worth it).

Because this operation affects the table it causes all dependent procedures located in shared pool to become invalid including the one which called this statement. But my main problem is when this procedure tries to call another one which is also dependant on the modified table. This is where I get my 4068 error. I tried to put a dynamic "ALTER PROCEDURE procname COMPILE" before the call, but it didn't work. Can you please help me with some solution?