Help! Stored procedure stops working

Oracle 7.3.2 / Solaris 2.5.1

About once every few months, a customer experiences a software glitch which I have traced to a specific database stored procedure. The error message implies that we are passing bad parameters, but I know for sure (from debug) that is not the case.

The error message produced by Oracle 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 the customer drops and re-creates the stored procedure. This indicates 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 utilities that could prevent this problem?

Thanks,
Jay Silverman
hjs_at_bellatlantic.net
jsilverman_at_excalib.com Received on Thu Jan 06 2000 - 16:57:39 CST