Re: Help! Stored procedure stops working

Yes, we had the exact same problem under Oracle 7.3.? / Solaris. It only happened to one particular procedure, and like you, the parameters passed to the procedure were definitely okay. We never did figure out what was causing this, but discovered that recompilng the package caused the errors to cease.

We ended up scheduling a DBMS_JOB to compile that package every night at midnight, and haven't seen the problem since. (Its been running error free now for over a year).

We just moved to Oracle 8.05 and my first task tomorrow is to remove the auto-recompile job and then see if the error comes back.

"Jay Silverman" <hjs_at_bellatlantic.net> wrote in message news:38762471.D12BACBB_at_bellatlantic.net...
> 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 Sun Jan 09 2000 - 23:39:54 CST