Re: Help! Stored procedure stops working

hi
we also have such a problem with a function that concatenates some string-values (either direct -> from dual, or from db-fields) in a well-defined manner.
every now and then your mentioned error-message occurs. we are working with applix 4.4.1 on hp-ux 10.x and oracle 7.3.3. the code is within a function, that is called by other functions and via applixware. we found out, that the function looses its grant, so entering grant execute on <function-name> to public solves the problem immediately.
we also refactored the function, setting string-lengthes higher and higher, but the problem stays in an unfriendly, 'statistically' manner...
greetings
hanspeter.schlapbach_at_sofim.com

In article <38762471.D12BACBB_at_bellatlantic.net>,   Jay Silverman <hjs_at_bellatlantic.net> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Feb 03 2000 - 03:16:58 CST