Help! Stored procedure stops working

Oracle 7.3.2 / Solaris 2.5.1

About once every few months, a regularly used database stored procedure stops working. The error message implies that bad parameters are passed, but I know for sure that is not the case.

The error message is:

 ORA-06502: PL/SQL: numeric or value error  ORA-06512: at line 1

I don't think that the procedure is executing at all. I think the error occurs on the procedure call.

The problem goes away when we drop and re-create the stored procedure. It seems to me that somehow Oracle has corrupted or lost the stored procedure's definition.

Any ideas? Has anyone heard of anything like this before? Are there tools available that would detect this condition, or perhaps some maintenance task that could prevent this problem?

Thanks,
Jay Silverman
hjs_at_bellatlantic.net
jsilverman_at_excalib.com Received on Fri Jan 07 2000 - 11:37:53 CST